count overdue dates and ignore blanks using countifs

vipa2000 · 09/16/20

So far I have this and spent 1/2 hour plus trying to understand isblank and error trapping in the forums but can't quite knit everything together.

=countifs(cc:cc,"120802",[Due Date], <=TODAY)

Background - cc column I want to show only 120802 and for the Due Date column only count those dates < today's date. My problem is not all tasks have allocated dates, so need to ignore.

I thought this would have been straightforward, but I am missing something.Thanks in advance Paul

Paul Newcome · 09/16/20

Try something like this...

=COUNTIFS(cc:cc, "120802", [Due Date]:[Due Date], AND(@cell <= TODAY(), @cell <> ""))

Paul Newcome · 09/17/20

I don't know of any documentation regarding that particular use of those functions. It is something that just kind of came from piecing different things together.

AND inside of the COUNTIFS just helps make sure that all criteria for the same range is kept together. This helps keep the formula organized if you have a lot of different ranges with multiple criteria sets for each.

<> simply means "not equal to". It is the same as using a NOT function except it helps cut out an extra set of those pesky parenthesis.

Paul Newcome · 09/16/20

Try something like this...

=COUNTIFS(cc:cc, "120802", [Due Date]:[Due Date], AND(@cell <= TODAY(), @cell <> ""))

vipa2000 · 09/17/20

Excellent Paul. Makes perfect sense when i read your code but couldn't find help that pulled countifs and And together with the <>. Looked it up again and still not obvious. Thanks again.

Paul Newcome · 09/17/20

I don't know of any documentation regarding that particular use of those functions. It is something that just kind of came from piecing different things together.

AND inside of the COUNTIFS just helps make sure that all criteria for the same range is kept together. This helps keep the formula organized if you have a lot of different ranges with multiple criteria sets for each.

<> simply means "not equal to". It is the same as using a NOT function except it helps cut out an extra set of those pesky parenthesis.

count overdue dates and ignore blanks using countifs

Best Answers

Answers

Help Article Resources

Categories