COUNTIFS on a cross sheet formula with ISDATE function

Marvin Daniels

=COUNTIFS({PBR}, main@row, {request date}, YEAR(@cell) = 2020)

The above works fine, but when I add an ISDATE function (below), fails.

=COUNTIFS({PBR}, main@row, {request date}, YEAR(@cell) = 2020, {complete date}, ISDATE(@cell))

What am I doing wrong? I only need to count records with a completion date. I've also played with {complete date}, NOT(ISBLANK(@cell) with no success.

Seems easy, so I'm sure I'm missing something simple. Help!

Paul Newcome

The syntax looks correct. When you say it fails, are you getting an unexpected count or an error?

Paul Newcome

The syntax looks correct. When you say it fails, are you getting an unexpected count or an error?

Marvin Daniels

Thanks Paul. I promise I tried this 10 times this morning with no success, but it's working like a charm now! No idea what I was doing wrong. Maybe I just needed to step away for a few hours! 🤪

Used this..

=COUNTIFS({PBR}, primary@row, {request date}, YEAR(@cell) = 2020, {completed}, ISDATE(@cell))

And a series of these for the months..

=COUNTIFS({PBR}, primary@row, {request date}, AND(YEAR(@cell) = 2020, MONTH(@cell) = 1), {completed}, ISDATE(@cell))

Paul Newcome

Sometimes sheets that have a lot going on can get a little buggy for a while, but if you leave it alone and come back later it is able to work itself out. One thing I have found that helps with this is to log out, clear my cookies and cache, then log back in.