SUMPRODUCT with IF

[virtuosok]

Hi,
I have the following working formula:

=SUMPRODUCT((--(IFERROR(MONTH(IWRS001!$Q$2:$Q$1000),0)=9)),--(IFERROR(YEAR(IWRS001!$Q$2:$Q$1000),0)=2020))

How/where can I add a condition please?

IF('IWRS001'!D2:D1000,"United States")

It doesn't work the way I thought it should (nor if I replace a comma after "States" by asterisk:

=SUMPRODUCT((--('IWRS001'!D2:D1000,"United States"),(IFERROR(MONTH(IWRS001!$Q$2:$Q$1000),0)=9)),--(IFERROR(YEAR(IWRS001!$Q$2:$Q$1000),0)=2020))

 

[AhoyNC]

Maybe something like this:

SUMPRODUCT((--(IWRS001!$D$2:$D$1000="United States"),--(IFERROR(MONTH(IWRS001!$Q$2:$Q$1000),0)=9)),--(IFERROR(YEAR(IWRS001!$Q$2:$Q$1000),0)=2020))

 

[virtuosok]

Maybe something like this:

SUMPRODUCT((--(IWRS001!$D$2:$D$1000="United States"),--(IFERROR(MONTH(IWRS001!$Q$2:$Q$1000),0)=9)),--(IFERROR(YEAR(IWRS001!$Q$2:$Q$1000),0)=2020))

Apparently Excel doesn't like this formula (neither as a plain formula nor as an array one)... I get a "there's a problem with this formula" error.

 

[jtakw]

Hi,

Try it this way, CSE:

=SUMPRODUCT(--($D$2:$D$10="United States"),--(IFERROR(MONTH($Q$2:$Q$10),0)=9),--(IFERROR(YEAR($Q$2:$Q$10),0)=2020))

 

[Eric W]

This also works, without the CSE or IFERRORs:

=SUMPRODUCT(--(IWRS001!D2:D1000="United States"),--(TEXT(IWRS001!Q2:Q1000,"mmyyyy")="092020"))

 

[virtuosok]

Thank you all!
Eric W, special thanks for trimming down the formula and allowing me to drop the CSE component!

 

[Eric W]

Glad we could help! Thanks for the update.