Sum multiple criteria with multiple ranges of criteria

[ddeboor]

I am trying to sum up values from a data set using multiple ranges of criteria. I am currently doing this using Sumifs but if the criteria set increases I have to be careful to add more sumifs to each formula.

Current Situation

1. 2 criteria sets
2. Each criteria from the first series needs to be checked against each of the second criteria
3. Using these a value is taken from the data set and summed up

I thought I could use a sumproduct but this does not seem to help as I still need one fixed criteria. Any thoughts that does not require multiple additional sheets

 

[ddeboor]

I am trying to sum up values from a data set using multiple ranges of criteria. I am currently doing this using Sumifs but if the criteria set increases I have to be careful to add more sumifs to each formula.

Current Situation

1. 2 criteria sets
2. Each criteria from the first series needs to be checked against each of the second criteria
3. Using these a value is taken from the data set and summed up

I thought I could use a sumproduct but this does not seem to help as I still need one fixed criteria. Any thoughts that does not require multiple additional sheets

Book1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
5
6																Data Set
7		Sum of Value			Criteria Set 1					Criteria Set 2						Set A	Set B	Value
8	Budget 1	15			1					A	B					1	A	5
9	Budget 2	6				2					B	C				1	B	10
10	Budget 3	44			1		3				B		D			1	C	15
11	Budget 4	54					3	4					D			2	A	25
12																2	B	6
13																2	D	8
14																3	C	4
15																3	D	34
16																3	A	86
17																4	A	200
18																4	C	50
19																4	D	20
20
Sheet1

Cell Formulas
Range	Formula
D8:D11	D8	=SUM(SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,O8),SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,N8),SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,M8),SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,L8))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,TRANSPOSE(L8:O8)))

 

[ddeboor]

Hi & welcome to MrExcel.
How about

=SUM(SUMIFS($T$8:$T$19,$R$8:$R$19,G8:J8,$S$8:$S$19,TRANSPOSE(L8:O8)))

Magic Last 2 hours puzzling solved

Thank you very much and for the welcome

 

[Fluff]

You're welcome & thanks for the feedback.