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
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 |