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

11
Comments

### Join the conversation!

Follow via:
RSS
Email Alert
View:
Show:
1 Vote
Moderator
###### SUMPRODUCT(MOD(ROW())) is one answer
Darryl~ 17th Feb 2012
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
2 Votes
###### Sum every other value (using an Array)
Rudi-S 20th Feb 2012
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)
0 Votes
Moderator
###### I can't get that to work without using SUMPRODUCT
Darryl~ 21st Feb 2012
but even so, that is a more compact way then I used.
0 Votes
###### Special array formula confirmation
Rudi-S 23rd Feb 2012
Hi Darryl,

Remember that an array formula needs to be confirmed with CTRL+SHIFT+ENTER, (not just Enter). The SUMPRODUCT function is a natural array formula and will work when you just press Enter.
0 Votes
###### =SUM(IF(ISEVEN(ROW(B1:B8)),B1:B8,0)) - as Array formula
david.white@... 21st Feb 2012
You could also use ISEVEN to replace MOD. Slightly shorter.
0 Votes
###### A different kind of answer
Mark.Mathews 21st Feb 2012
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.
0 Votes
###### If you really must
Mark.Mathews 21st Feb 2012
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.
0 Votes
###### A simple solution that is easier to follow
Suhel_M 21st Feb 2012
=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.
0 Votes
###### A Simple Solution
sk.tomar@... Updated - 21st Feb 2012
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
0 Votes
###### Votes please
sk.tomar@... 23rd Feb 2012
No one voted for it :-[
0 Votes
###### DSUM with a custom list
Mark.Mathews 22nd Feb 2012
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.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.