Excel SUMPRODUCT works for 2021, but not 2020

user_2867882 0
Viewed 55
=SUMPRODUCT(--(YEAR(Infra_SIM[Request Date])=2021),--(TEXT(MONTH(Infra_SIM[Request Date]),"mmm")="Jan"),--(Infra_SIM[Use For Reporting]="Yes"))

This uses the named table and works fine, gives 13 as expected.

=SUMPRODUCT(--(YEAR(Infra_SIM[Request Date])=2020),--(TEXT(MONTH(Infra_SIM[Request Date]),"mmm")="Dec"),--(Infra_SIM[Use For Reporting]="Yes"))

Same exact formula for 2020, doesn't work. It gives me 0 and it should give 12.

What could possibly be the problem?

excel-formula excel
1 Answers

It's not the year that's the problem, it's the month.

You wrapped it in the TEXT function and it's not evaluating as an array (it's all returning the first date).

To fix it change

--(TEXT(MONTH(table[column]),"mmm"="Dec"))

To

--(MONTH(table[column])=12)

Edit:

BigBen has pointed out that the issue isn't the TEXT() function, it's that MONTH() and TEXT() are nested in each other.

--(TEXT(table[column],"mmm")="Dec") is an equally valid solution.

user_115005010
Related