Sumproduct + sumifs array

[Vbanoob98]

Hi everyone,

So basically Im trying to use a sumifs formula to include 2 different arrays as conditions, it works perfectly when using 1 array, but as soon as I add a second one, I stop getting the results I want.

Ive attached the minisheet below, if someone can explain why its not working it would really help me out, thanks!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Partner	Scenario	Probability	Total									Scenario Array	Probability Array
2	James	1	High	10				Scenario	2				1	High
3	James	2	Medium	30				Probability	Medium				2	Medium
4	James	2	High	15				Total	40
5	James	1	Medium	8
6
7								Expected
8								Scenario	2
9								Proability	Medium
10								Total	63
11
Sheet1

Cell Formulas
Range	Formula
I4	I4	=+SUMPRODUCT(SUMIFS(D2:D5,C2:C5,IF(I3="High",N2,N2:N3),B2:B5,IF(I2=1,M2,M2:M3)))

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=$M$2:$M$3
I3	List	=$N$2:$N$3

 

[Eric W]

It has to do with how SUMIFS handles array variables for the parameters. SUMIFS can handle 1 vertical array (N2:N3) only. But it can also handle 1 vertical and 1 horizontal array:

=+SUMPRODUCT(SUMIFS(D2:D5,C2:C5,IF(I3="High",N2,N2:N3),B2:B5,IF(I2=1,M2,TRANSPOSE(M2:M3))))

You can convert M2:M3 from vertical to horizontal with the TRANSPOSE.


Edit: you can also use SUMPRODUCT without the SUMIFS:

=SUMPRODUCT(D2:D5,--ISNUMBER(MATCH(C2:C5,N2:N3,0)),--ISNUMBER(MATCH(B2:B5,M2:M3,0)))

You can use something like that to extend your formula to include other arrays.