Need help with SUMPRODUCTS to use 2-3 criteria

[Akbarov]

Hello community,

I am trying to sum values in column E
If column C is September
Also column C 2022
Until month everything was working fine, but when I added 1 more criteria ( Year ) i get error. Can anyone help me please?

=SUMPRODUCT(Sheet2!$E$2:$E$3000,--( TEXT(Sheet2!$C$2:$C$3000,"MMMM")=Dashboard!$K$2),--(YEAR (Sheet2!$C$2:$C$3000)=M2))

 

[SanjayGMusafir]

=SUMPRODUCT(Sheet2!$E$2:$E$3000,--(TEXT(Sheet2!$C$2:$C$3000,"MMMM")=Dashboard!$K$2)*(YEAR (Sheet2!$C$2:$C$3000)=$M$2))

But would recommend using SUMIFS that is far more stable, if you have to bring up a column total based on multiple criteria

 

[KRice]

The issue I see with the original formula is an extra space between YEAR and ( needs to be deleted.

 

[Akbarov]

KRice is right. Thanks both for reply

 

[KRice]

You're welcome...we're happy to help.

 

[Peter_SSs]

=SUMPRODUCT(Sheet2!$E$2:$E$3000,--( TEXT(Sheet2!$C$2:$C$3000,"MMMM")=Dashboard!$K$2),--(YEAR (Sheet2!$C$2:$C$3000)=M2))

What sheet is the red M2 above on? I'm guessing it might be on 'Dashboard' like the K2?
.. and that would mean that this formula is also on sheet 'Dashboard'?

If my guesses are correct, what about the shorter ..

=SUMPRODUCT(Sheet2!$E$2:$E$3000,--( TEXT(Sheet2!$C$2:$C$3000,"MMMMYYYY")=$K$2&M2))