Microsoft

Office solution: How to sum every other value in a column

This week, learn the solution to last week's challenge: How would you sum every other value in a column?

Last week, I asked you how you'd sum every other value in a column. Suhel_M was the first to suggest the idea I had in mind - we both used a helper column. Many of you suggested an array. We'll discuss both solutions, but first, let's review the helper column solution, shown below. The formula in column D

=MOD(ROW(B2),2)

returns 0 or 1. The formula in cell C10

=SUMIF(C2:C9,"=1",B2:B9)

sums the values in column B where the corresponding value in column C equals 1. This formula starts summing with the second value in the data range, not the first. You could easily change the outcome by specifying 0 in the criteria argument, "=0", instead.

Helper columns are a personal preference; whether supporting users or creating custom applications, helper columns seem easier to manage and maintain. Several of you suggested arrays, which are a great tool in the right hands! Darryl~ was the first to offer an array solution

=SUMPRODUCT((MOD(ROW(B2:B9)-ROW(B2),2)=1)*(B2:B9))

(I changed the 0 to 1 in the MOD() function.)  Rudi-S's array is a bit shorter

=SUM((MOD(ROW(B2:B9),2)=1)*B2:B9)

Remember to press [Shift]+[Ctrl]+Enter when using array formulas.

David.White suggested using ISEVEN() instead of MOD(), which is a good alternative to MOD() in both solutions, and one I hadn't considered. Sk.tomar's solution was close to the helper column that Suhel_M and I both like, but uses literal criteria values and a DSUM() function.

Thanks for another interesting challenge. You provided a number of interesting approaches.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments