nested countif with date criteria

Kasey Bernard

I'm trying to do a countif statement that checks if a request is approved or denied, and the return to work date is great than today's date. I've tried multiple ways and can't figure it out. Any ideas?

Genevieve P.

Hi @Kasey Bernard

It looks like you only want to calculate this data per-row, is that correct? For that row, see if the Denied Request is "False", and then also check if in that same row, the Return to Work Date is greater than Today.

If so, you could actually just use an IF formula, since there will always only be one Row:

=IF(AND([Denied Request]@row = "false", [Return to Work Date]@row > TODAY()), 1, 0)

Then drag-fill this down the entire column. This will return a 1 if both of the criteria are met (you could have your column be a checkbox column so the box is checked, if you want).

Or, if you were looking to count a total across the entire sheet for how many rows there are where these two criteria are met, you can use a COUNTIFS statement, but reference whole columns:

=COUNTIFS([Denied Request]:[Denied Request], "false", [Return to Work Date]:[Return to Work Date], > TODAY())

Here are some Help Articles I used to build these formulas: IF Function / AND function / @row function / Reference Columns / COUNTIFS function

Let me know if you have any questions about either formula!

Cheers,

Genevieve