IF using using multiple COUNTIFS functions

Hi, this may be a simple solution but I've been stuck for a while now.

I'm trying to get a count of dates within that are before today's date, within 30 days from today, between 30 & 60 days away and further than 60 days away.

The catch is that I need to use two columns (planned dates and revised dates). If there is a revised date, I want the formula to only use the revised date but if the revised date cell is blank, to use the planned date.

There are more categories in the counts (hence I'm using COUNTIFS) but I haven't included them as they are not what's causing the issue, they're just identifiers so it's counting the right category.

I just want to isolate them so it's counting using one column (P "Revised Date") or the other (O "Planned Date").

I've added verbally what I'm trying to make it do and the formula I'm using.

Really appreciate any help on this one because I think my head is about to pop!


Before Today:
"If column P has a value (aka: there is a date there), count how many are before today's date. If it doesn't have a value, count the number of dates in column O that are before today's date."

=IF($P:$P="<>",COUNTIFS($P:$P,"<"&TODAY()),COUNTIFS($O:$O,"<"&TODAY())



Within 30 days from now:
"If column P has a value, count how many are coming up in the next 30 days. If it doesn't have a value, count the number of dates in column O that are coming up in the next 30 days."

=IF($P:$P="<>",COUNTIFS($P:$P,"<="&TODAY()+30),COUNTIFS($O:$O,"<="&TODAY()+30))



Between 30 & 60 days away:
"If column P has a value, count how many are between 30 to 60 days away. If it doesn't have a value, count how many of the dates in column O that are between 30 to 60 days away."

=IF($P:$P="<>",COUNTIFS($P:$P,">="&TODAY()+30,$P:$P,"<="&TODAY()+60),COUNTIFS($O:$O,">="&TODAY()+30,$O:$O,"<="&TODAY()+60))



Further than 60 days away:
"If column P has a value, count how many are 60 days or above away. If it doesn't have a value, count how many dates on column O are more than 60 days away."

=IF($P:$P="<>",COUNTIFS($P:$P,">"&TODAY()+60),COUNTIFS($O:$O,">"&TODAY()+60))

Thanks for the response!

That would have been the case if only one of the columns was populated. The issue is that there is always a date in column O. But if I add a date to column P, then your formula will count both.

I want it to count P if there is a value in it (and ignore O), but if P is blank, to only count column O.

See what I mean?

Sorry - been trying different thinks in the formula. We're getting closer...

The IF statement at the start is still not ignoring the O column if P is populated for the other options (less than 30 days, etc.)

So if the P is before today, and O is in the future, it is still counting before today as 1 and the one in the future as 1 as well.

Attached is a sample.

So in it the A is O and B is P.

Thanks Sambo kid, really appreciate your help.

I've adjusted the data instead to make it very easy rather than trying to be fancy with the formula.

Basically I just renamed it "Anticipated completion date" instead of Revised and populated that entire column with the Planned dates as well. That way I only need to count what is in that one column instead and will have to track changes made to the anticipated a bit more carefully.