Discussion on:

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

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

You can use an array formula:
=SUM((MOD(ROW(B2:B9),2)=0)*B2:B9)

If you need to sum up the ODD row values, then use:
=SUM((MOD(ROW(B2:B9),2)=1)*B2:B9)

but even so, that is a more compact way then I used.

You could also use ISEVEN to replace MOD. Slightly shorter.

The formulas presented here may work for those who know how to use them. However, someone who's asking for this kind of solution may not be so knowledgable.

Sometimes, instead of trying to figure out how to do what the customer asks for, it may be a better idea to give them a simpler solution. In this case, you may want to suggest that instead of summing up every even row, ask them to separate their values into two columns.

I've been thinking about this problem some more.

If the user genuinely does need to keep the values of the even numbered rows in the same column with the values on the odd numbered rows, to keep things simple, let's put a formula in another column to pull out the needed data: IF(ISEVEN(ROW(B2)),B2,"") - Then, we can simply sum that column.

It's rarely a good idea to give a highly sophisticated formula to someone who doesn't understand it. In addition, if the summed value is critical, it's always a good idea to have a simple way to validate it.

=MOD(B1,2) returns 1 for odd rows and 0 for even row. Do this calculation in column C.

Then use =SUMIF(C1:C37,0,B1:B37) to sum even rows, or =SUMIF(C1:C37,1,B1:B37) for odd rows.

This provides a simpler formula that is easy to follow and should be easier to troubleshoot.

Please prepare your worksheet as per following steps. This me look like a long process but quite simple to understand.

1. Add a row at the top of data, and a column on left of your existing data.
2. Type 1,2,1,2,.... in freshly created column (leftmost), give it a lable e.g. code. similarly give labels to other 2 existing columns e.g. name and amount. in D column first row (D1), Type Code (label of first column) and in D2 type 1.
3. Now your worksheet should look like:

A B C D E
1 Code Name Amount Code
2 1 Bill 87 1
3 2 Susan 73
4 1 Alexis 26
5 2 Alexis 75
6 1 Kate 19
7 2 Bill 33
9 1 Susan 25
10 2 Kate 82
11
12 Total of alternates =DSUM(A1:C9,C1,D1:D2)

(key in above formula in C12, where you want to calculate total of rows with code 1)
And you have the result.
To calculate rows with code 2, simply change value in D2 to 2.
Happy learning

Sandeep K Tomar

Thanks Sandeep for the DSUM formula. Very cool.

Here's another approach using the same formula. Instead of typing in 1,2,1,2, over and over. Create a custom list with the text EVEN, ODD.
Type EVEN in cell A2 and use the fill handle to fill in the rest of the column to the bottom of the data range (especially useful for a long list).
Now, in your column D2, you can type either EVEN or ODD and you'll get the same result.