COUNTIFS reference other cell, count cells with BLANK value in a column, part of formula works
I want to count how many cells are blank after they meet the range 1 and range 2 criteria. The formula works without the last part which is count blank cells. When I add the last part to the formula, it gives me #UNPARSEABLE error. What am I doing wrong?
=COUNTIFS({Range1}, "xyz", {Range2}, "abcd", {Range3}, <>"")
=COUNTIFS({Range1}, "xyz", {Range2}, "abcd", {Range3}, ISBLANK@cell)
Thanks
Answers
-
All you missed are the parentheses for the ISBLANK function:
=COUNTIFS({Range1}, "xyz", {Range2}, "abcd", {Range3}, ISBLANK(@cell))
-
Looks like you're missing a set of parentheses on the ISBLANK(@cell):
=COUNTIFS({Range1}, "xyz", {Range2}, "abcd", {Range3}, ISBLANK(@cell))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Leibel Shuchat Thanks for a prompt response. I corrected the formula and it still doesn't work 🙁
-
@Jeff Reisman Thanks for your response. I corrected the formula and it still doesn't work 🙁
-
Can you provide a snapshot of the ranges and data you're working with? What are the column types your ranges are referencing?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
The column types are simple drop down values.
Now the error is incorrect argument set
=COUNTIFS({Request Type}, "enterprise", {status}, "not started", {Range3},ISBLANK(@cell))
-
Check you cross sheet references, are they all the same size?
-
@Leibel Shuchat not sure if I understood your question, what are you referring to when you say check if all are same size?
-
@Aparna Singh The three ranges need to be the same size. So if you selected the entire column from the remote sheet for one range, you need to select the entire column for the other ranges, or if you've only selected a subset of rows, you need to select the same subset for the other ranges.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Got it, yes I selected the whole column for all three.
-
@Aparna Singh Are there any cells showing errors in the column ranges you are trying to count?
If not, can you add a temporary column in the sheet that contains your ranges, and try the following in one of the rows:
=COUNTIFS([Request Type]:[Request Type], "Enterprise", [Status]:[Status], "Not Started", Priority:Priority, ISBLANK(@cell))
Use the above formula exactly as I typed it, and see what result you get.
Here's what I get on my test sheet:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
This worked. Thanks for all your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!