Countif with IsDate & IsBlank
For my summary sheet:
I'm trying to count rows where there is a date selected (the assigned to OSU row) but no region selected (Region row).
I tried this but it did not work:
=COUNTIF(ISDATE([Assigned To OSU]@row), "cell contains a date",(ISBLANK([Region]@row))
In another summary row, I then want it to count the number of flags raised.
Best Answers
-
Hello Tabitha!
I think you will need to add a helper column to get the count you want!
Here is how I would approach it:
- Add a column called something like "Assigned but No Region". I would make this a checkbox column with a column formula set up as =IF(AND(ISDATE([Assigned To OSU]@row), ISBLANK(Region@row)), true, false)
- Run your count off of that new column using this formula =COUNTIF([Assigned but No Region]:[Assigned but No Region], true)
To count the flags raised in a symbol column using the flag symbol use this formula: =COUNTIF([Raise Flag]:[Raise Flag], 1)
Let me know if that helps!
-
I see. I think I mixed up your requirements there. I had the is date and Isblank requirements flip-flopped. Here is the correct version.
=COUNTIFS(Region:Region, ISBLANK(@cell), [Assigned to OSU]:[Assigned to OSU], ISDATE(@cell))
Answers
-
Hello Tabitha!
I think you will need to add a helper column to get the count you want!
Here is how I would approach it:
- Add a column called something like "Assigned but No Region". I would make this a checkbox column with a column formula set up as =IF(AND(ISDATE([Assigned To OSU]@row), ISBLANK(Region@row)), true, false)
- Run your count off of that new column using this formula =COUNTIF([Assigned but No Region]:[Assigned but No Region], true)
To count the flags raised in a symbol column using the flag symbol use this formula: =COUNTIF([Raise Flag]:[Raise Flag], 1)
Let me know if that helps!
-
Actually, I believe this is possible without a helper column. Try this:
=Countifs(Region:Region, Isdate(@cell), [Assigned to OSU]:[Assigned to OSU], Isblank(@cell))
Did that work?
-
Mike's solution is definitely more elegant! I forgot about @cell there for a minute. 😊
-
Thanks for the help from both of you. It's really appreciated!
Lauren, I was able to get yours to report out correctly.
Mike, your's is not unparsable, but it also isn't returning my test record.
See below
-
I see. I think I mixed up your requirements there. I had the is date and Isblank requirements flip-flopped. Here is the correct version.
=COUNTIFS(Region:Region, ISBLANK(@cell), [Assigned to OSU]:[Assigned to OSU], ISDATE(@cell))
-
It works! Thanks again, you rock!
-
Awesome. Glad I could help you out! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!