Solved

SUM every other column

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

{=SUM(IF(MOD(COLUMN(K4:JS4),2)=0,K4:JS4),0)}

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

Todd
Sales-and-Rebates.xlsx
0
Comment
Question by:thutchinson
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 23

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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41881015
Cell [JT4] ("Total Sales") is currently this (array) formula:
=SUM(IF(MOD(COLUMN(J4:JR4),2)=0,J4:JR4),0)
= 1,094,640.12

Cell [JU4] ("Total Rebates") is this (array) formula:
=SUM(IF(MOD(COLUMN(K4:JS4),2)=0,K4:JS4),0)
= 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
0
 

Author Comment

by:thutchinson
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.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 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))
0
 

Author Comment

by:thutchinson
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41881634
If you are looking for the odd column pairs, you can use this in JU
{=SUM(IF(MOD(COLUMN(K4:JS4),2)=1,K4:JS4),0)}

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 41881635
Another way of doing these JT and JU formulas is to look at row 3:
{=SUM(IF(ISNUMBER($J$3:$JR$3),J4:JR4),0)}
{=SUM(IF(ISNUMBER($K$3:$JS$3),0,K4:JS4))}

Open in new window

0
 
LVL 32

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.

=SUM(IF(MOD(COLUMN(K4:JS4),2)=1,K4:JS4),0)

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.

Thanks
Rob H
0
 
LVL 21

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.
0
 

Author Closing Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

816 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now