[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SUM every other column

Posted on 2016-11-09
10
Medium Priority
?
64 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
[X]
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
10 Comments
 
LVL 25

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Accepted Solution

by:
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))
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 46

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 46

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

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

Author Closing Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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