Sumproduct with multiple criteria

[bdhogan]

Hi all,

Long time reader, first time poster and was hoping someone may be able to help me out.

I am attempting to convert the following successful formula:

=SUMPRODUCT((A10:A20={"Yes","No","Maybe"})*(B10:B20))

to

=SUMPRODUCT((A10:A20=A1:A3)*(B10:B20))
where
A1=Yes
A2=No
A3=Maybe

Is this possible?

 

[Haseeb Avarakkan]

Hello, Welcome to the board.

Try,

=SUMPRODUCT(--ISNUMBER(MATCH(A10:A20,A1:A3,0)),B10:B20)

 

[bdhogan]

BRILLIANT Haseeb!

I couldn't quite workout the syntax to use match successfully for my formula. Now to expand it out!

Thanks again!