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]

I can't find a way to post a screenshot

Go here: https://onedrive.live.com/?id=8CFFDE...FFDEC0CE27E813

download the first folder, extract, then go to file-options-add ins-manage add ins-browse-click on the file wherever you saved it-be sure it's checked in the add ins, and it should appear on the ribbon if you have Excel 2007 or later, otherwise in the tools menu.

To post on the board with it, highlight the range (not too large, there's like a 100 row limit and too many columns are hard to see), then go to add-ins - mrexcel htlml - generate html - go to MrExcel message page and control-V

 

[Thomasms90]

Go here: https://onedrive.live.com/?id=8CFFDE...FFDEC0CE27E813

download the first folder, extract, then go to file-options-add ins-manage add ins-browse-click on the file wherever you saved it-be sure it's checked in the add ins, and it should appear on the ribbon if you have Excel 2007 or later, otherwise in the tools menu.

To post on the board with it, highlight the range (not too large, there's like a 100 row limit and too many columns are hard to see), then go to add-ins - mrexcel htlml - generate html - go to MrExcel message page and control-V

I'm on a corporate computer so I can't install any addins or third party software, unfortunately

 

[sheetspread]

The link didn't completely print above, it's:

https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813

 

[sheetspread]

I'm on a corporate computer so I can't install any addins or third party software, unfortunately

Ok, then can you copy and paste the smallest sample possible that has the before/after desired structure?

 

[Thomasms90]

Ok - something like below, where array C is just very long and array B may change over time.

I need to sum the values in array C that corresponds to the Lower bounds and upper bounds (LB and UB) in Array B for each type in Array A. Hence, there is in most cases several ranges for each type.

Array A: Sums		Array B: Identifiers		Array C: Data range	
Type	Sum		LB	UB	Type		ID	Value
a	17.859.514	1000	1099	a		1000	94.320
b			1100	1249	a		1001	73.011
c			1250	1499	f		1002	74.188
d			1500	1519	e		1003	11.627
e			1520	1599	c		1004	48.696
f			1600	1699	d		1005	58.817
			1700	1999	b		1006	44.725
			2000	2499	c		1007	11.037
			2500	2999	e		1008	1.992
			3000	3099	a		1009	30.449
			3100	3499	f		1010	70.497
							1011	93.524
							1012	69.277
							1013	79.498
							1014	72.081

 

[Roger Govier]

Hi
Copying your data into my workbook and parsing it, I ended up with the LB's in column D starting at D3, and UB's in column E starting at E3.

ID started in H3 and Value in I3

With a to f in A3:A8 the following formula in B3 and copied down should give your desired results.
Sumifs can take whole columns as the objects, and there are only 2 columns of interest, ID and Value
=SUMIFS(I:I,H:H,">="&D3,H:H,"<="&E3)

 

[Thomasms90]

Hi
Copying your data into my workbook and parsing it, I ended up with the LB's in column D starting at D3, and UB's in column E starting at E3.

ID started in H3 and Value in I3

With a to f in A3:A8 the following formula in B3 and copied down should give your desired results.
Sumifs can take whole columns as the objects, and there are only 2 columns of interest, ID and Value
=SUMIFS(I:I,H:H,">="&D3,H:H,"<="&E3)

Hi Roger,

Thanks for your reply, but it does not solve my issue, unfortunately. The complication is exactly that I have the Array B in which there may or may not be several ranges that I need to sum, depending on the type of the object.

For type a it would need to sum the values in your column I whenever the ID is in the ranges 1000<=X<=1099, 1100<=X<=1249, and 3000<=X<=3099.

 

[Marcelo Branco]

A very simple solution is to use a Helper column. Something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]

Type​

[/td][td]

Sum​

[/td][td] [/td][td]

LB​

[/td][td]

UB​

[/td][td]

Type​

[/td][td]

Helper​

[/td][td]

ID​

[/td][td]

Value​

[/td][/tr]


[tr][td]

2​

[/td][td]

a​

[/td][td]

833.739​

[/td][td] [/td][td]

1000​

[/td][td]

1099​

[/td][td]

a​

[/td][td]

833739​

[/td][td]

1000​

[/td][td]

94.320​

[/td][/tr]


[tr][td]

3​

[/td][td]

b​

[/td][td] [/td][td] [/td][td]

1100​

[/td][td]

1249​

[/td][td]

a​

[/td][td]

0​

[/td][td]

1001​

[/td][td]

73.011​

[/td][/tr]


[tr][td]

4​

[/td][td]

c​

[/td][td] [/td][td] [/td][td]

1250​

[/td][td]

1499​

[/td][td]

f​

[/td][td]

0​

[/td][td]

1002​

[/td][td]

74.188​

[/td][/tr]


[tr][td]

5​

[/td][td]

d​

[/td][td] [/td][td] [/td][td]

1500​

[/td][td]

1519​

[/td][td]

e​

[/td][td]

0​

[/td][td]

1003​

[/td][td]

11.627​

[/td][/tr]


[tr][td]

6​

[/td][td]

e​

[/td][td] [/td][td] [/td][td]

1520​

[/td][td]

1599​

[/td][td]

c​

[/td][td]

0​

[/td][td]

1004​

[/td][td]

48.696​

[/td][/tr]


[tr][td]

7​

[/td][td]

f​

[/td][td] [/td][td] [/td][td]

1600​

[/td][td]

1699​

[/td][td]

d​

[/td][td]

0​

[/td][td]

1005​

[/td][td]

58.817​

[/td][/tr]


[tr][td]

8​

[/td][td] [/td][td] [/td][td] [/td][td]

1700​

[/td][td]

1999​

[/td][td]

b​

[/td][td]

0​

[/td][td]

1006​

[/td][td]

44.725​

[/td][/tr]


[tr][td]

9​

[/td][td] [/td][td] [/td][td] [/td][td]

2000​

[/td][td]

2499​

[/td][td]

c​

[/td][td]

0​

[/td][td]

1007​

[/td][td]

11.037​

[/td][/tr]


[tr][td]

10​

[/td][td] [/td][td] [/td][td] [/td][td]

2500​

[/td][td]

2999​

[/td][td]

e​

[/td][td]

0​

[/td][td]

1008​

[/td][td]

1.992​

[/td][/tr]


[tr][td]

11​

[/td][td] [/td][td] [/td][td] [/td][td]

3000​

[/td][td]

3099​

[/td][td]

a​

[/td][td]

0​

[/td][td]

1009​

[/td][td]

30.449​

[/td][/tr]


[tr][td]

12​

[/td][td] [/td][td] [/td][td] [/td][td]

3100​

[/td][td]

3499​

[/td][td]

f​

[/td][td]

0​

[/td][td]

1010​

[/td][td]

70.497​

[/td][/tr]


[tr][td]

13​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]

1011​

[/td][td]

93.524​

[/td][/tr]


[tr][td]

14​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]

1012​

[/td][td]

69.277​

[/td][/tr]


[tr][td]

15​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]

1013​

[/td][td]

79.498​

[/td][/tr]


[tr][td]

16​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]

1014​

[/td][td]

72.081​

[/td][/tr]


[tr][td]

17​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in G2 (Helper) copied down
=SUMIFS(I:I,H:H,">="&D2,H:H,"<="&E2)

Formula in B2 copied down
=SUMIF(F:F,A2,G:G)

Hope this helps

M.

 

[Roger Govier]

Hi Thomas

Sorry, I had not noticed that column F (in my data set) was not a contiguous range of a ... f
I see that there are several discontiguous range than belong to a, etc.

That being the case, the only solution I can see is a manual addition of Sumifs formulae
=SUMIFS(I:I,H:H,">="&D3,H:H,"<="&E3)+SUMIFS(I:I,H:H,">="&D4,H:H,"<="&E4)+SUMIFS(I:I,H:H,">="&D12,H:H,"<="&E12)

Obviously such a formula cannot be dragged down and would have to be constructed for each cell in B3:B8

What would be better would be to set up a lookup table, and use an extra column.
I set up a table in cells K1:L12 to hold my lookup table (pasted here horizontally for convenience)
K1 holds 0 and L1 holds a etc. L12 is blank

[TABLE="width: 792"]
<colgroup><col width="72" span="11" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 72"]0[/TD]
[TD="class: xl66, width: 72"]1249[/TD]
[TD="class: xl66, width: 72"]1499[/TD]
[TD="class: xl66, width: 72"]1519[/TD]
[TD="class: xl66, width: 72"]1599[/TD]
[TD="class: xl66, width: 72"]1699[/TD]
[TD="class: xl66, width: 72"]1999[/TD]
[TD="class: xl66, width: 72"]2499[/TD]
[TD="class: xl66, width: 72"]2999[/TD]
[TD="class: xl66, width: 72"]3099[/TD]
[TD="class: xl66, width: 72"]3499[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]

[/TD]
[/TR]
</tbody>[/TABLE]
Then in column J, starting in J3 the formula
=VLOOKUP(H3,$K$1:$L$11,2,1) and copied down

In B3 the formula then becomes simply a SUMIF formula as follows
=SUMIF(J:J,A3,I:I)
copied down through B4:B8