I want to use SUMPRODUCT with multiple criteria

[dfida]

Excel Workbook
	Y	Z	AA
2		GROUP	VALUE
3		A	23
4		B	45
5		C	23
6		D	6
7		A	78
8		B	6
9		G	7
10		D	44
11		E	12
12
13		A
14		B
15		C
16		current formula:	175
17
18		desired formula:	#N/A
I'm sure this has been asked before, but I've searched and searched and not found anything... Basically I want to create a sumproduct formula which uses multiple criteria from a specific range. Right now I have to specify each criteria individually within the sumproduct, which is cumbersome when I have to use more that just a few - sometimes I may have up to 20. Formula AA16 shows how I am doing it now. AA18 is how I want to do it, although I know that formula doesn't actually work. Hopefully it illustrates the gist of what I want to accomplish. 136-1

Excel 2007

Cell Formulas
Range	Formula
AA16	=SUMPRODUCT(--(($Z$3:$Z$11=Z13)+($Z$3:$Z$11=Z14)+($Z$3:$Z$11=Z15)),$AA$3:$AA$11)
AA18	=SUMPRODUCT(--($Z$3:$Z$11=$Z$13:$Z$15),$AA$3:$AA$11)

 

[MrRajKumar]

Try this.

=SUMPRODUCT(--ISNUMBER(MATCH($Z$3:$Z$11,$Z$13:$Z$15,0)),$AA$3:$AA$11)

Raj

 

[dfida]

Try this.

=SUMPRODUCT(--ISNUMBER(MATCH($Z$3:$Z$11,$Z$13:$Z$15,0)),$AA$3:$AA$11)

Raj

Thanks Raj...it worked great! You sure made it look easy!

 

[MrRajKumar]

Since you have Excel 2007, this will be faster,

=SUMPRODUCT(SUMIFS($AA$3:$AA$11,$Z$3:$Z$11,$Z$13:$Z$15))

 

[dfida]

Thanks, will try that one and report back.