Sumproduct using arrays of different sizes

[exoticdisease]

I have an array of criteria and a criteria range within which to match these criteria and a set of values to sum when the criteria are matched. The array of criteria is approx 20 items, the range of criteria and the sum range are approx 10,000 items (same length)

Array of criteria (e.g. a, b, c, d, e, f...)
Criteria range (e.g. a, a, a, a, b, b, c, c, d, d, e, e, e, f, g, g, d, d, a, d, e, g, h, )
Sum range (e.g. 1, 2, 3, 4, 5, 6, 7....)

I want to do this using sumproduct but I don't want to write out the criteria range = first criteria, criteria range = second criteria, criteria range = third criteria etc....

Is there a way to write the sumproduct where you can input an array of criteria to be compared with the criteria range?

Thanks!

Array of criteria		Criteria Range	Sum Range
PE		PE	0.48319752​
OTS		OTS	0.62705098​
WWD		WWD	0.86165507​
WXG		WXG	0.48221693​
		L/NAT	0.84089828​
		L/NAT	0.95185048​
		OTS	0.90960647​
		JAN	0.0971486​
		APR	0.1447644​
		MAY	0.57678618​
		OTS	0.68916729​
		SEP	0.59132988​
		DEC	0.48391153​
		TUE	0.47043671​
		SAT	0.95928812​
		SUN	0.11259954​

 

[Norie]

Try this.

=SUMPRODUCT(--(ISNUMBER(MATCH($B$2:$B$17,$A$2:$A$5,0))),Sheet3!$C$2:$C$17)

 

[exoticdisease]

Brilliant, thank you! Is a property of ISNUMBER that it forces match to calculate as an array? I tried to input IFERROR (I understand that this will not give the desired result, it was academic!) but it stopped MATCH evaluating as an array and just gave #VALUE as the result of the lookup_value in the MATCH. Do you know why? Does IFERROR override the array nature of SUMPRODUCT?

Thank you again

 

[Norie]

Why did you try IFERROR?

Did ISERROR work?

 

[exoticdisease]

Just out of curiosity mostly... I was impressed with the eloquence of the above formula so I thought I'd see how it might be altered for any future uses I might have - IFERROR was having none of it.

ISERROR worked perfectly...I had to put a NOT in, was very confused for a moment!

 

[Norie]

had to put a NOT

Oops, should have mentioned that.

 

[jasonb75]

This will also work and may calculate more efficiently with the volume of data mentioned in post 1.

=SUMPRODUCT(SUMIF(C2:C17,A2:A5,D2:D17))