Countif with IsDate & IsBlank

Tabitha W.

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.

Lauren Kleitz

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:

1. 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)
2. 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!

Mike Wilday

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

Lauren Kleitz

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:

1. 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)
2. 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!

Mike Wilday

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?

Lauren Kleitz

Mike's solution is definitely more elegant! I forgot about @cell there for a minute. 😊

Tabitha W.

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

Mike Wilday

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

Tabitha W.

It works! Thanks again, you rock!

Mike Wilday

Awesome. Glad I could help you out! :)