SUMPRODUCT to lookup based on multiple criteria - how does it work?

[andrewc]

Hi,

I have a formula =SUMPRODUCT((range1=criterion1)*(range2=criterion2)*(data range)) that performs a lookup in a data range subject to two criteria.

This formula returns the correct answer but could someone please explain how it works?

Thanks!

[Fotis1991]

Best site for any query-question about SUMPRODUCT is Bob's Phillips site.

[martindwilson]

read here ,scroll down to "sumproduct explained"
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[Bob Phillips]

range1 is compared to criterion1 and builds an array of TRUE/FALSE values
ditto range2 and criterion2
These are multiplied together to create an array of 1/0 values
This array is multipled by the values range, data range, to build an array of values that meet the criteria (where the two previous tests were TRUE) and zeros
Sumporiduct then sums this array.

For a more detailed, far more detailed, explanation, see http://www.xldynamic.com/source/xld.sumproduct.html

[Fotis1991]

Nice to see you back here Bob!

[Bob Phillips]

Thanks. I have been busy, but it is quieter now.

It is good to get back into the swing

I know it is still difficult in your homeland, but hopefully things are on the up there, however slowly.

[andrewc]

Thanks for the quick response! The article you that referred me to is very helpful.

Can I please ask one more question related to this.

In the attached file I have used the SUMPRODUCT function both to add and count the values in column C that meet the criteria to be product AAA sold in NYC. Can someone please tell me how I adjust the formula so that I can perform a lookup using these criteria? I appreciate that there is more than one instance that meets the criteria so I would expect the lookup to return the first value it finds in column C, ie. 4.

Thanks!

[Fotis1991]

If the teacher is online, then he is the boss on this!


Edit: We need many years for this Bob. Thanks for your kind comment.

[martindwilson]

for the match
=INDEX(C2:C7,MATCH("aaa"&"-"&"nyc",INDEX(A2:A7&"-"&B2:B7,0),0))
but fyi you dont need sum product in excel 2010
=SUMIFS($C$2:$C$7,$A$2:$A$7,"aaa",$B$2:$B$7,"nyc")
=COUNTIFS($A$2:$A$7,"aaa",$B$2:$B$7,"nyc")

[andrewc]

Thank you all!

[Fotis1991]

You are welcome-from all of us-- and thanks for the feedback.