Can SUMPRODUCT change the range it uses?

[joeq]

Hi all - I'm wondering about a variation of SUMPRODUCT:

If this is typical.............

=SUMPRODUCT(--($A20:$A58=$H$2),$H20:$H58,M106:M144)

What about the scenario where I don't need to match H2 to A20:A58 at all, but rather I just need a way to say:

If H2 = "Home", then use H20:H58 where I use it above.
If H2 = "Car", then use I20:I58 instead.
If H2 = "Lawn", then use J20:J58.

I don't think I can nest an IF since I have 10 possible H2's and IF only allows 7 nests.

Thanks, Joe

 

[just_jon]

certainly... do you want to PM me your address?

Yes, please.