Solved

SUM every other column

Posted on 2016-11-09
10
47 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 22

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

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

Industry Leaders: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 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