Help w/ COUNTIFS and ISBLANK and NOT ISBLANK

jmo

Hi team - I have 2 columns (Seat and Open Req) that I need to count:

When Seat = Filled and Open Req is not blank

When Seat = Filled and Open Req is blank

I'm struggling with the COUNTIFS sequence to get this to work.

Any help would be greatly appreciated.

Gil Nash

Without knowing all of the possible options for Seat and Open Req, this may not work for you but you'll need something like this.

jmo

Hi @Gil Nash - the Seat column options are: Filled, Open, Departed. The Open Req column is just a text field that will either have an alphanumeric number or be blank.

So I'm looking to COUNTIF Seat = Filled and NOT ISBLANK.

Does that help? Make sense?

Gil Nash

As long as the [Open Req] is always a number, this will work. There's also a ISTEXT option so you could add an OR statement to the 2nd IF statement that states OR(ISNUMBER[Open Req]@row, ISTEXT[Open Req]@row.

jmo

Unfortunately the Open Req column has letters and numbers (ie: R0004321)

Gil Nash

https://community.smartsheet.com/discussion/comment/257973#Comment_257973

See the 2nd statement about the ISTEXT and OR function.

Paul Newcome

You could also try these...

[Open Req] is NOT Blank:

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "")

[Open req] is Blank:

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "")

Combining the two:

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "") + COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "")

OR

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], OR(@cell <> "", @cell = ""))

Gil Nash

jmo

@Gil Nash - I tried to replicate your last formula across my specific column but got an unparseable error:

The formula I typed out was (the image has the full/correct column names:

=IF(AND(seat@row, "filled", ISBLANK([Open Position Req #]@row)), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(seat@row = "filled", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(seat$1:seat@row, "filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))

Do I have a missing parenthesis or something?

Gil Nash

=IF(AND(Seat@row = "Filled", ISBLANK([Open Position Req #]@row)), COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISBLANK(@cell)), IF(AND(Seat@row = "Filled", OR(ISNUMBER([Open Position Req #]@row), ISTEXT([Open Position Req #]@row))), COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISNUMBER(@cell)) + COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))

jmo

Nope - still unparseable @Gil Nash 🤔

Gil Nash

https://community.smartsheet.com/discussion/comment/258192#Comment_258192

@jmoser

Your first IF(AND statement:

=IF(AND(seat@row, "filled" should become =IF(AND(seat@row = "filled"

The end of your formula:

ISTEXT(@cell)), "")) should become ISNUMBER(@cell)) + COUNTIFS(Seat$1:Seat@row, "Filled", [Open Position Req #]$1:[Open Position Req #]@row, ISTEXT(@cell)), ""))

Gil Nash

@jmoser

I just noticed in your screenshot that you are using the summary field. My formula will NOT work in a summary field.

You'll have to set up 2 fields (1 for Blank Open Position Req # and 1 for NOT Blank) and use the formulas from @Paul Newcome

NOT BLANK field:

=COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISNUMBER(@cell)) + COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISTEXT(@cell))

BLANK field:

=COUNTIFS(Seat:Seat, "Filled", [Open Position Req #]:[Open Position Req #], ISBLANK(@cell))

jmo

Ah ok - that makes a lot more sense.

Thanks for the assistance @Gil Nash

Paul Newcome

I actually just realized I had a typo in one of my formulas. Here they are corrected:

[Open Req] is NOT Blank:

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell <> "")

[Open req] is Blank:

=COUNTIFS(Seat:Seat, "Filled", [Open Req]:[Open Req], @cell = "")

Using <> "" is the same as NOT(ISBLANK(@cell)) or combining the two COUNTIFS for ISTEXT(@cell) and ISNUMBER(@cell) except it saves quite a few keystrokes and gets rid of some of those pesky parenthesis.

Same for = "" replacing ISBLANK(@cell)