Help w/ COUNTIFS and ISBLANK and NOT ISBLANK

jmo · 07/28/20

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 · 07/28/20

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 · 07/28/20

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 · 07/28/20

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 · 07/28/20

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

Gil Nash · 07/29/20

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

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

Paul Newcome · 07/29/20

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 · 07/29/20

jmo · 07/29/20

@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 · 07/29/20

=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 · 07/29/20

Nope - still unparseable @Gil Nash 🤔

Gil Nash · 07/29/20

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 · 07/29/20

@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 · 07/29/20

Ah ok - that makes a lot more sense.

Thanks for the assistance @Gil Nash

Paul Newcome · 07/30/20

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)

Help w/ COUNTIFS and ISBLANK and NOT ISBLANK

Answers

Help Article Resources

Categories