# Help w/ COUNTIFS and ISBLANK and NOT ISBLANK

✭✭✭✭✭✭

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.

• ✭✭✭

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.

• ✭✭✭✭✭✭

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?

• ✭✭✭

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.

• ✭✭✭✭✭✭

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

• ✭✭✭

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

• ✭✭✭✭✭✭

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 - 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?

• ✭✭✭

=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)), ""))

• ✭✭✭✭✭✭

Nope - still unparseable @Gil Nash 🤔

• ✭✭✭

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)), ""))

• ✭✭✭
edited 07/29/20

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))

• ✭✭✭✭✭✭

Ah ok - that makes a lot more sense.

Thanks for the assistance @Gil Nash

• ✭✭✭✭✭✭

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 Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!