COUNTIFS using (TODAY()-5days) Filter or equivalent

**Stymple Tweed** · 05-14-2013, 05:07 PM

Assuming Column A is Date of Assigned Task, Column B is Person Assigned to Task and Column C is % Task Completion, how do you count matching criteria per row: Person, % Task Completed AND differentiate between old (>5 days from current date) and new (<5 days from current date) projects

COUNTIFS works well, except for trying to differentiate between old and new projects. I can't seem to use the "TODAY or NOW() function minus "X" number of days" together with COUNTIFS.

I need to be able to track completion of projects, but also to differentiate between new and old assignments.

Please see attached sample file.

BTW, first time ever posting on any forum whatsoever. So please excuse the "noobiness"

**Jonmo1** · 05-14-2013, 05:13 PM

You got 2 problems

=COUNTIFS(B5:B24,"Jim",C5:C24, "<100",A2:A24,">(TODAY()-5)")

1 the ranges need to be the same size
You have Column B and C using row 5 to 24, but A is using row 2 to 24

2 You need to concatenate the TODAY()-5 with the ">" string.
Instead of ">(TODAY()-5)"
it would be
">"&TODAY()-5

So try
=COUNTIFS(B2:B24,"Jim",C2:C24, "<100",A2:A24,">"&(TODAY()-5))

**AZ-XL** · 05-14-2013, 05:38 PM

In your file
f21 cell =COUNTIFS($B:$B,F20,$A:$A,">="&TODAY(),$A:$A,"<"&TODAY()+5,$C:$C,"<100") then drag right

f 24 cell =COUNTIFS($B:$B,F20,$A:$A,"<="&TODAY(),$A:$A,">"&TODAY()-5,$C:$C,"<100") then drag right

**Stymple Tweed** · 05-15-2013, 08:34 AM

Thanks guys!

In the actual file I had already ensured that the ranges were the same size, but must have overlooked this in the sample file I sent.

Using "<="&TODAY()-5 as the final range criteria worked. This should also help with getting the right syntax the next time I need to combine functions.

I appreciate the help.