Countif with multiple criteria based on date-CodePudding

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?

CodePudding user response：

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),"")