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.
Answers
-
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)), ""))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!