Countif with multiple criteria based on date

Question

I have difficulties with the following scenario.

I need to get the amount (count) of the product ordered in a specific time range (today + 8 days back).

• Column [A] consists of the order date.
• Column [B] conssists of the product being ordered.
• Column [C] should give me the amount ordered within the last 8 days.
• Column [D] is my current result.

The problem is that the results in column [D] are wrong according to my need. In row 7 you can see that this product was ordered twice in the last 8 days, but this is wrong.

Second example (colored orange) is correct, but only because both dates are within the range of past 8 days.

My current formula in column [D] is:

=IF(A2>=TODAY()-8,COUNTIF(B$2:B$10,B2),"")

I think it's not possible to use COUNTIFS (multiple criteria) in combination with the date restriction, right? What is the correct approach for this use case?

Answer 1

Yes you can use a formula in the criteria, The range part has to be a range not an array.

COUNTIFS(B$2:B$10,B2,$A$2:$A$10,">="&TODAY()-8)

So:

=IF(A2>=TODAY()-8,COUNTIFS(B$2:B$10,B2,$A$2:$A$10,">="&TODAY()-8),"")