SUMIFS with Criteria

[kumara_faith]

Hi,

I have the following tables:

Book2
	D	E	F	G	H	I	J	K	L	M
4	Table 1				Table 2			Table 3		Table 4
5	State	Sales	Product		Product	State		Results		Results
6	Alabama	500	T1		T1	Alabama		1300		5800
7	Texas	2500	V5		V5			4500
8	Washinton	6000	H2
9	Idaho	7000	T1
10	Hawaii	1200	K4
11	Georgia	550	M9
12	Alabama	800	T1
13	Oregon	900	B4
14	Alabama	4500	V5
Sheet1

Cell Formulas
Range	Formula
K6:K7	K6	=SUMIFS(E6:E14,D6:D14,I6,F6:F14,H6:H7)
Dynamic array formulas.

1) I have my raw data in Table 1
2) In Table 2, I have my calculation criteria where I would to like to sum the sales for the state of Alabama, for product type T1 and V5
3) In table 3, I have the formula. However, it seems to spill the results in two rows instead of one
4) The correct results is in Table 4.

Is there a way to modify the formula to obtain the correct results as per Table 4 ? Appreciate all the help.

 

[JamesCanale]

Old school sumproduct

MrExcelPlayground6.xlsx
	D	E	F	G	H	I	J	K	L	M
4	Table 1				Table 2			Table 3		Table 4
5	State	Sales	Product		Product	State		Results		Results
6	Alabama	500	T1		T1	Alabama		5800		5800
7	Texas	2500	V5		V5
8	Washinton	6000	H2
9	Idaho	7000	T1
10	Hawaii	1200	K4
11	Georgia	550	M9
12	Alabama	800	T1
13	Oregon	900	B4
14	Alabama	4500	V5
Sheet21

Cell Formulas
Range	Formula
K6	K6	=SUMPRODUCT(E6:E14,SIGN((D6:D14=I6)+(D6:D14=I7)),SIGN((F6:F14=H6)+(F6:F14=H7)))

 

[Fluff]

Another option

=SUM(SUMIFS(E6:E14,D6:D14,I6,F6:F14,H6:H7))