SUMIF where range and sum_range are in every nth column

kristian97

New Member
Joined
Nov 12, 2019
Messages
5
Hello!
I would like to get result of 19 in B7 for sum up values where criteria is always banana (A7) but there are multiple ranges(A,E,I) and sum_ranges(C,G,K) which are in every nth column.

A B C D E F G H I J K
1 apple n/a 1 cherry n/a 1 banana n/a 10
2 banana n/a 5 lemon n/a 1 orange n/a 1
3 orange n/a 1 orange n/a 1 lemon n/a 1
4 lemon n/a 1 banana n/a 4 cherry n/a 1
5 cherry n/a 1 apple n/a 1 apple n/a 1
6
7 banana 19

Thank you for any help.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,293
Welcome to the MrExcel Board.

Here are a couple options:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">apple</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">cherry</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">banana</td><td style=";">n/a</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">banana</td><td style=";">n/a</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style=";">lemon</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">orange</td><td style=";">n/a</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">orange</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">orange</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">lemon</td><td style=";">n/a</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">lemon</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">banana</td><td style=";">n/a</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">cherry</td><td style=";">n/a</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">cherry</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">apple</td><td style=";">n/a</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">apple</td><td style=";">n/a</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">banana</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A1:I5=A9</font>),C1:K5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">OFFSET(<font color="Green">A1:A5,0,{0,4,8}</font>),A9,OFFSET(<font color="Green">C1:C5,0,{0,4,8}</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Either one works, but depending on other factors, such as table size, one might be preferable over the other.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,935
Office Version
365, 2010
Platform
Windows
For just 3 ranges, I would do this:

=SUMIF(A:A,"Banana",C:C)+SUMIF(E:E,"Banana",G:G)+SUMIF(I:I,"Banana",K:K)
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,490
For this particular case, the formula is rather simple:

=SUMIF(A:I,A7,C:K)
 

kristian97

New Member
Joined
Nov 12, 2019
Messages
5
Thank you very much for the solution.
The first formula works better because column count is actually much higher than in my question.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,293
Glad we could help! :)

A few thoughts though. First, my first formula, and Tetra's formula, both have an issue that if "banana" occurs in another column (B,C,D,F,G,H, etc.) you could get erroneous results. If that's an issue, my second formula only looks at the specific columns you want (A, E, I, etc.). Adding more columns is just a matter of updating the offsets in the array variables {0,4,8}, or we could update the formula like this:

=SUM(SUMIF(OFFSET(A:A,0,(ROW(A1:A3)-1)*4),A9,OFFSET(C:C,0,(ROW(A1:A3)-1)*4)))

and confirm it with Control+Shift+Enter. Then you just need to change the red 3s to the number of columns you want to look at.

And second, if "banana" in the other columns isn't an issue, then I suspect Tetra's formula would be more efficient on large tables, although I haven't timed it. If your spreadsheet starts to lag, you might want to try that one.
 

Forum statistics

Threads
1,089,394
Messages
5,407,994
Members
403,176
Latest member
mehtavish1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top