Simplify Sumproduct formula

[dlmoore99]

I'm trying to simplify a Sumproduct formula but can't figure out how to do so. current formula is:

=SUMPRODUCT((H3:H100=$K$3)*(E3:E100="Dunkin")+(H3:H100=$K$3)*(E3:E100="t-bones")+(H3:H100=$K$3)*(E3:E100="hannafords")+(H3:H100=$K$3)*(E3:E100="starbucks")+(H3:H100=$K$3)*(E3:E100="lowes")+(H3:H100=$K$3)*(E3:E100="subway"),C3:C100),

have tried following , but with #VALUE error:

=SUMPRODUCT((H3:H100=$K$3)*(E3:E100={"Dunkin","t-bones","hannafords","starbucks","lowes","subway"}),C3:C100)

thank you for any help in the matter

 

[RoryA]

Try using:

=SUMPRODUCT((H3:H100=$K$3)*(E3:E100={"Dunkin","t-bones","hannafords","starbucks","lowes","subway"})*C3:C100)

 

[dlmoore99]

Thank you so much, but why does the ),C3:C100) at the end of another formula work: =SUMPRODUCT((H3:H100=$K$3)*(D3:D100="Yes"),C3:C100) and not in this case

 

[RoryA]

Because of the criteria array.

 

[dlmoore99]

so if it is just one criteria the , should work and need * with multiple criteria, or just use * no matter the criteria

 

[RoryA]

If you use commas, all the arrays must have the same dimensions. When you use a criteria array like that you end with an array with multiple rows and columns, but your data array is just one column, so you get an error. By multiplying, SUMPRODUCT actually only has one array passed to it, and simply totals it.

 

[dlmoore99]

ok, thanks for the explanation, helps to wrap it around the wee lil brain still learning all the nuances of excel

 

[RoryA]

still learning all the nuances of excel

Me too. It's only been 30 years...