Sumproduct with Array Constants as argument not working

[lynnsong986]

Hello, I have the data as attached.

the formula is:


=SUMPRODUCT((D2:F8)*(C2:C8={"My new line","Your New Line"})*(D1:F1)=H1)

which is not working. here I'm trying to calculate sum of 2017 revenue for "My new line" and "Your New Line". "H1" has "2017" in it.

the {"My new line","Your New Line"} part needs to stay in this format as this is returned by a userform where they pick what revenue item(s) to sum.

can someone please help? the formula didn't work either way as a regular nor an array formula.

thanks!

[Bo_Ry]

Please try

=SUMPRODUCT(($D$2:$F$8)*isnumber(match($C$2:$C$8,{"My new line","Your New Line"},))*($D$1:$F$1=H1))

[Ochimus]

lynnsong,

Not quite clear on this? If you are looking for only the sum of 2017 revenue for the selected elements in Col C, why are you including the values for 2018 and 2019 as well?

Ochimus

[CARACALLA]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[lynnsong986]

thanks so much Bo_Ry!! your formula works like a charm!!

the formula is written not in the year columns, that's why I need to use sumproduct in this way.