SUM every other column

Posted on 2016-11-09
Medium Priority
Last Modified: 2016-11-14
I am trying  to use following formula to add every other column in an Excel spreadsheet:


Can anyone look at the formula in Col JU and tell me why it's not working?  Thank you for any help!

Question by:thutchinson
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 24

Expert Comment

by:Brian B
ID: 41880965
The total of K4:JS4 is 1,108,573.58 and your formula is returning 1,094,640.12 as the sum of every other cell. So it is definitely not picking up all the numbers, but it's hard for me to tell if it is successfully returning what you expect. What should the total be?

Edit: Your formula is different than mine. Brackets are in a different place. I made the following example:
1 1 2 2 3 3 4 4 5 5 6 6 {=SUM(IF(MOD(COLUMN(A1:L1),2)=0,A1:L1,0))}
Which returns 21 as expected.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41881015
Cell [JT4] ("Total Sales") is currently this (array) formula:
= 1,094,640.12

Cell [JU4] ("Total Rebates") is this (array) formula:
= 1,094,640.12

As TBone2K mentioned, replacing your formulae with a different approach, I receive different values:

[JT4]: =SUMIF(J1:JR1,"<>""",J4:JR4)
= 1,108,570.75

[JU4]: =SUMIF(K1:JS1,"<>""", K4:JS4)
= 1,108,573.58

Author Comment

ID: 41881072
Total sales is a large number.  Total rebates will be a vastly smaller number.   I'm getting the same number from my formulas so something is definitely wrong.  I don't know what the number is supposed to be.  That's what I'm trying to find out.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 41881152
Formula in JT4 must be slightly changed
Is =SUM(IF(MOD(COLUMN(K4:JS4),2)=0,K4:JS4),0)
and must be =SUM(IF(MOD(COLUMN(J4:JR4),2)=0,J4:JR4,0))
,0 is part of the if formula, so the bracket must be after 0

The formula use the even columns, but in JU4 you must use the uneven columns.
So either =SUM(IF(MOD(COLUMN(K4:JS4),2)<>0,K4:JS4,0))
or =SUM(IF(MOD(COLUMN(K4:JS4),2)=1,K4:JS4,0))

Author Comment

ID: 41881430
I've put all the suggested formulas in and I'm not getting the correct values.  I tried modifying the ranges so that I could verify the values but no joy.  I appreciate any suggestions.  Thanks for the help.
LVL 46

Expert Comment

ID: 41881634
If you are looking for the odd column pairs, you can use this in JU

Open in new window

LVL 46

Expert Comment

ID: 41881635
Another way of doing these JT and JU formulas is to look at row 3:

Open in new window

LVL 33

Expert Comment

by:Rob Henson
ID: 41881908
I agree with Ejgil suggestion above, the MOD part of the function needs to return 1 for odd columns.


Changing to that in the sheet gives a result of 13933.46 on row 4.

Physically highlighting every cell in the rebates columns for that row also gives the same result on the status bar sum.

After changing the formula you need to confirm with Ctrl + Shift + Enter, not just Enter.

Confirming with just Enter gives 1108573.58

Copying down to row 64 gives a total for column JU of 1388767.18 which matches your total in row 67.

Rob H
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 41882539
Sorry I did not specifically state that it is array formulas.
Assumed you knew, since the original formulas was array formulas.

Author Closing Comment

ID: 41886438
This works! (although I entirely understand how.) Thank you for your heip. -Todd

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question