Sumproduct returning 0 with date

[grahambza]

Hi all
I am having an issue with Sumproduct returning a 0 when I apply a date criteria

I tested the date format with countif and it works but gives me a 0 with sumproduct (and Countifs)

Below is some sample data with the formula I am using. Cell I18 = February 2022 which is the date i am searching for, with correct formatting applied
Any advice would be appreciated

sample data.xlsx
	D	E	F	G	H	I	J	K
12
13	Column 1	Month detected	Month Resolved	Severity		Same problem with sample data
14		Jan-22		Critical
15		Jan-22		Critical		=SUMPRODUCT((e17:$e25=i18)*(g17:$g25="Critical"))
16		Jan-22		Critical		0
17		Jan-22		Critical
18		Jan-22	Jan-22	Critical		Feb-22	Date I am looking for
19		Jan-22	Jan-22	Critical
20		Feb-22		High
21		Feb-22		High
22		Feb-22		High
23
Scratchpad

Cell Formulas
Range	Formula
I16	I16	=SUMPRODUCT((E14:$E22=I18)*(G14:$G22="Critical"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I18	Expression	=$E18="Resolved"	text	NO
I18	Expression	=$E18="Resolved"	text	NO
I18	Expression	=$E18="Resolved"	text	NO
F18:F19	Expression	=$E18="Resolved"	text	NO
F18:F19	Expression	=$E18="Resolved"	text	NO
F18:F19	Expression	=$E18="Resolved"	text	NO
E20:E22	Expression	=$E20="Resolved"	text	NO
E20:E22	Expression	=$E20="Resolved"	text	NO
E20:E22	Expression	=$E20="Resolved"	text	NO
E15:E19	Expression	=$E15="Resolved"	text	NO
E15:E19	Expression	=$E15="Resolved"	text	NO
E15:E19	Expression	=$E15="Resolved"	text	NO
E14	Expression	=$E14="Resolved"	text	NO
E14	Expression	=$E14="Resolved"	text	NO
E14	Expression	=$E14="Resolved"	text	NO

 

[Alex Blakenburg]

What am I missing here ? Your example formula is looks for Feb 2022 AND "Critical".
This combination does not exist in your sample data so I would expect it to return zero.

If I change the Critical to High it works fine.

 

[grahambza]

UGH rookie error

While trying to generate better sample data I figured out that the formula worked when I linked to a cell ref but not when i entered "February 2022"

 

[Alex Blakenburg]

Thanks for letting me know. Glad you got it sorted.