SUMIF where range and sum_range are in every nth column

kristian97

New Member
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Eric W

MrExcel MVP
Welcome to the MrExcel Board.

Here are a couple options:

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

Scott Huish

MrExcel MVP
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
For this particular case, the formula is rather simple:

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

kristian97

New Member
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
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.