SUMIF where range and sum_range are in every nth column

kristian97

New Member
Joined
Nov 12, 2019
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the MrExcel Board.

Here are a couple options:


Book1
ABCDEFGHIJK
1applen/a1cherryn/a1bananan/a10
2bananan/a5lemonn/a1orangen/a1
3orangen/a1orangen/a1lemonn/a1
4lemonn/a1bananan/a4cherryn/a1
5cherryn/a1applen/a1applen/a1
6
7
8
9banana19
1019
Sheet2
Cell Formulas
RangeFormula
B9=SUMPRODUCT(--(A1:I5=A9),C1:K5)
B10=SUMPRODUCT(SUMIF(OFFSET(A1:A5,0,{0,4,8}),A9,OFFSET(C1:C5,0,{0,4,8})))


Either one works, but depending on other factors, such as table size, one might be preferable over the other.
 
Upvote 0
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:
Upvote 0
Thank you very much for the solution.
The first formula works better because column count is actually much higher than in my question.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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