Sum if based on ranges of conditions

[Thomasms90]

Hi all,

I need to do a SUMIF that is rather advanced compared to what I'm used to since it must compare arrays of different sizes (or match them sequentially). I can't find a way to post a screenshot nor mock file, so I hope below is understandable.

I have an array A that consists of different types a, b, c, d, e, f.

Then an array B that contains ranges (lower and upper bounds) of unique identifiers that are connected with either a, b, c, d, e, or f (i.e. 1000 to 1099 belongs to a, 1100 to 1249 belongs to a, and 3000 to 3099 belongs to a, and so forth for other types).

Finally an array C that contains a very large data range of a given identifier and an associated value.

I need to do a sumif of the values in array C, given that the identifier is within any of the given ranges in array B that are associated with a given type.

Currently, my only solution is to split it up into several formulae parts, where I have 1 SUMIFS() for each identifier in array B that matches the type in array A. But this is unfeasible in my actual workbook and very manual.

Is there a way to do this in one formula? I've tried SUM(IF()) arrays, but that won't work as it usually does, since the arrays are not of the same size.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G
1	Sums		Identifiers	range
2	Type	Sum	LB	UB	Type	ID	Value
3	a	12	1000	1099	a	1000	3
4	b	1	1100	1249	a	1200	1
5	c	17	1250	1499	f	1400	8
6	d	10	1500	1519	e	1600	10
7	e	10	1520	1599	c	1800	1
8	f	20	1600	1699	d	2000	2
9			1700	1999	b	2200	8
10			2000	2499	c	2400	7
11			2500	2999	e	2600	2
12			3000	3099	a	2800	8
13			3100	3499	f	3000	8
14						3200	7
15						3400	5
16						3600	9
17						3800	9
Sheet8 (5)

Cell Formulas
Range	Formula
B3	=SUMPRODUCT($G$3:$G$17,COUNTIFS($C$3:$C$13,"<="&$F$3:$F$17,$D$3:$D$13,">="&$F$3:$F$17,$E$3:$E$13,A3))