Using SUMIF with a non-sequential SUM range

waterconsultant

New Member
Joined
Oct 18, 2016
Messages
18
Hi Folks, probably trivial, but not familiar with non-sequential ranges. Assuming I have 2 columns, X and Y, with 5 rows of data, 1 to 5. Col X is the lookup range, Col Y is the sum range. If the criteria in lookup range is "A2", and this occurs on e.g. row 2 and row 5, then obviously =SUMIF(X1:X5,"A2",Y1:Y5) produces the correct result finding the associated values in col Y.

BUT in my case the sum range is NOT sequential, and is elsewhere. For example, let's say the values all occur in column F, but now have multiple rows between the lookup values i.e. the 5 potential lookup values occur on row 7, 14, 21, 28, 35? How to access THAT 'range'? (There will always be a fixed number of interval rows, if that helps). I tried giving the non-sequential lookup range a NAME, as in SUMIF(X1:X5,"A2",rangename) but no joy.

Thanks in advance!
waterconsultant
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I would consider using a helper column, say column Z, with this formula in Z1, copied down as far as your column X values (row 5 in your example).
=INDEX(Y:Y,ROWS(Z$1:Z1)*7)

Then you could use
=SUMIF(X1:X5,"A2",Z1:Z5)
 
Upvote 0
Thanks for your reply Peter. Unfortunately it doesn't really move me forward as I HAVE to use the lookup values in their fixed position. It would be of course very simple to copy the discontinuous values to a condensed array, and then sue the SUMIF as normal, but I don't have this option because I am tight for space!
Thanks Brian
 
Upvote 0
I tried to simulate the possible data you have.
The result is in cell Z2, obtained with an array formula. I also assume, according to your example, that the sum is every 7 rows.
Check if this helps you, tell me and help you adapt the formula to your data.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:76.04px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:97.9px;" /><col style="width:18.06px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >E</td><td >F</td><td >G</td><td >W</td><td >X</td><td >Y</td><td >Z</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffc000; font-weight:bold; text-align:center; ">CRITERIA</td><td style="background-color:#ffc000; font-weight:bold; "> </td><td style="background-color:#ffc000; font-weight:bold; "> </td><td style="background-color:#ffc000; font-weight:bold; text-align:center; ">VALUE</td><td style="background-color:#ffc000; font-weight:bold; "> </td><td style="background-color:#ffc000; font-weight:bold; "> </td><td style="background-color:#ffc000; font-weight:bold; text-align:center; ">RANGE</td><td style="background-color:#ffc000; font-weight:bold; "> </td><td style="background-color:#ffc000; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >data2</td><td > </td><td > </td><td style="text-align:right; ">36</td><td > </td><td > </td><td >data1</td><td > </td><td style="background-color:#92d050; text-align:right; ">51</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td style="text-align:right; ">47</td><td > </td><td > </td><td style="background-color:#ffff00; ">data2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="text-align:right; ">13</td><td > </td><td > </td><td >data3</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td style="text-align:right; ">45</td><td > </td><td > </td><td style="background-color:#ffff00; ">data2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td style="text-align:right; ">42</td><td > </td><td > </td><td >data5</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td style="text-align:right; ">64</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td style="text-align:right; ">63</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">36</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td style="text-align:right; ">39</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td > </td><td > </td><td style="text-align:right; ">22</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">15</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td > </td><td > </td><td > </td><td style="text-align:right; ">14</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td > </td><td > </td><td > </td><td style="text-align:right; ">17</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td > </td><td > </td><td > </td><td style="text-align:right; ">75</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td > </td><td > </td><td > </td><td style="text-align:right; ">21</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td > </td><td > </td><td > </td><td style="text-align:right; ">36</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td > </td><td > </td><td > </td><td style="text-align:right; ">24</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >Z2</td><td >{=SUM(IFERROR(SUBTOTAL(9,OFFSET(F1,IF(X2:X6=A2,(ROW(X2:X6)-1)*7)-1,0)),0))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Upvote 0
Thanks for your reply Peter. Unfortunately it doesn't really move me forward as I HAVE to use the lookup values in their fixed position. It would be of course very simple to copy the discontinuous values to a condensed array, and then sue the SUMIF as normal, but I don't have this option because I am tight for space!
Thanks Brian
Perhaps I haven't understood correctly but ..
- I thought you said the lookup range was column X, and I am not suggesting moving or doing anything with that range.
- The column of formulas I suggested for condensing the sum range could be placed in a hidden column, thereby not impacting you issue of being "tight for space". That is unless you are already using all 16,384 columns. :eek:

A clearer picture of exactly what you have, where, and what you are trying to do and/or small sample dummy data might help. Otherwise in trying to set up some sample data as Dante & I have done we are really just guessing.
 
Last edited:
Upvote 0
Thanks Peter, agreed, sample data wou,d bve much more helpful but I have no idea how to paste in a JPG to the forum. It just seems to want a link, rather than being able to paste direct??? Excuse my ignorance.
Brian
 
Upvote 0
Thanks Dante. Phew! It will take me quite a while to decipher your solution, but thanks. Seeing that my lookup range is in same column and equally spaced apart does suggest that OFFSET + ROWS might be a solution, but I need to understand what your code does first!
Thanks. Brian
 
Upvote 0
Dear All, so as not waste everyone's precious time, a very clunky solution to my own trivial problem, sincere apologies if I did not explain it very well. 2 columns of data, of which Column X is the 'lookup' range and can have any text value A0 to A4, say, and Column Y is the sum_range with values which have a fixed (but non-contiguous) location, see text below.
X Y
A0 3
A2 7
A0 5
A4 15
A2 4

and I simply want the sub-total values of Column Y if the criteria test is "A2" or "A0" or "A1" etc. etc. In this case, normally =SUMIF(X1:X5,"A2",Y1:Y5) will give the correct answer for "A2", 7+4=11. However my problem was that the actual column sum_range is NOT contiguous i.e. the five values are separated by large gaps (let's say in Column Z at Row 1, 8, 16, 24, 32), and I can't/didn't want to use a hidden column to condense them back to a sequential column as per Y above, which of course is the obvious trivial workaround.

There very probably is a better more elegant way, (although non-contiguous ranges in EXCEL seem quite problematic (to me:(), but this suffices for me: (where the Z column contains the same numbers as in Column Y above but separated out).

=SUMIF(X1,"A2",Z1) + SUMIF(X2,"A2",Z8) + SUMIF(X3,"A2",Z16) + SUMIF(X4,"A2",Z24) + SUMIF(X5,"A2",Z32). This has to be repeated (in my case) in five locations in order to pick up all possibilities of A0 to A4, substituting the " "criteria as necessary, but that's OK as I have 5 such locations, one for each. So the end result of all such tests would be A0=8, A2=11, A4=15, all other tests are zero.

and this also means that the 'sum_range' individual values can be distributed anywhere in the spreadsheet, which is very flexible. Clunky but robust.
Thanks as always to the dedicates who take time out to help intellectually challenged individuals such as myself!
 
Upvote 0
The concept is clear: you need to make a sum. You no longer have to explain it.


As long as you don't give clear examples, let's continue guessing.

---------------
your can posting a small, copyable, screen shot

https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


or

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Thanks Dante. Phew! It will take me quite a while to decipher your solution, but thanks.
Does that mean Dante's formula is working for you? (I think it can, with a minor tweak or two - see below)

i.e. the five values are separated by large gaps (let's say in Column Z at Row 1, 8, 16, 24, 32),
I hope that is a typo as the first gap there is 7 rows while all the other gaps are 8 rows.

You also seem to be changing columns a lot, as you have mentioned these spaced out sum values in columns Y, F and Z so far. Bit hard to keep up with. ;)

Anyway, for the layout Dante gave in post 4 (but changed data), his formula needed one small change, that is the OFFSET should have been from F2, not F1 see below. (Assuming I have understood the requirement correctly :eek:)

For my sample data below, I believe you would want the sum of the yellow cell (56).
You will see that Dante's original formula (AA2) is 3 off as it is adding the 3 cells above the yellow ones but with the adjustment to OFFSET from F2 (AA3) the result is correct.

In AA4 I have suggested further slight changes. I have included the lookup value in the formula itself as that you seemed to be trying to do with your formulas and I have also added security in case rows are subsequently added above the data. If you add a new row 1, you will see that the first 2 results in column AA will change while the third one remains at 56.

Remember that all of these formulas require confirmation with Control+Shift+Enter, not just Enter

Excel Workbook
AFGWXYZAAAB
1
2A21A253Original formula from post 4
32B156Original formula from post 4 with OFFSET changed to F2
43A256Lookup value directly in formula & robust against row insertion
54A2
65B1
76
87
98
109
1110
1211
1312
1413
1514
1615
1716
1817
1918
2019
2120
2221
2322
2423
2524
2625
2726
2827
2928
3029
3130
3231
3332
3433
3534
3635
SUMIF disjoint ranges
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top