Microsoft

Office challenge: How would you sum every other value in a column?

This week, test your Excel skills with this unusual problem. Can you help this user sum every other value?

Users often have unusual requests. For instance, how would you help a user who wants to sum every other value in a column. If the column is short, you can press [Ctrl] and click every other cell, as shown below.

If the column of values is long, this technique is not only tedious, it's easy to click the wrong cells! Can you come up with a solution that's easier to implement than the [Ctrl]+click technique? There's probably more than one way to do this and there are no restrictions on your approach.

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.

1 comments
Darryl~
Darryl~

The formula =MOD(B1,2) would return 0 if the number is even and 1 if it's odd, now, if we use the ROW() function instead of B1, we can figure out if a row is even or odd......once we know that, we can use the SUMPRODUCT() so we can sort of set it up as sort of an array. In the screenshot in the article they are selecting the even rows in the B column and the range is B2:B9 so the final formula that "should" work would be: =SUMPRODUCT((MOD(ROW(B2:B9)-ROW(B2),2)=0)*(B2:B9)) I think :D