Counting based on 2 criteria 1 being date exactly 3 months today

[AndrewD04]

Good Morning,

Sorry i cant upload a copy of excel my system just wont allow it.

I am trying to count how many times the word Valid appears in column E of Sheet 2 if Column B matches P8 of Sheet 1 and Column C of sheet 2 is exactly 3 months from today not including days.

=SUMPRODUCT((Sheet 2!E:E="Valid")*(Sheet 2!C:C>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1)*(Sheet 2!B:B='Sheet 1'!P8)))
=AND(Sheet 2!E:E="Valid", Sheet 2!C:C>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,1), ISNUMBER(MATCH('Sheet 1'!P8, Sheet 2B:B, 0)))

These are just 2 ways i have tried and both are returning False for all lines even though there is a match.

Thanks in advance

Dan

 

[RoryA]

The first one should work to return any matching data with dates after 1st April 2023, which is of course not "exactly 3 months from today". It's also not a good idea to use SUMPRODUCT with entire columns, so you could use countifs instead:

=COUNTIFS('Sheet 2'!E:E,"Valid",'Sheet 2'!C:C,|">="&DATE(YEAR(TODAY()),MONTH(TODAY())-3,1),'Sheet 2'!B:B,'Sheet 1'!P8)