The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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

{=SUM(IF(MOD(COLUMN(K4:JS4

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

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)

Which returns 21 as expected.

=SUM(IF(MOD(COLUMN(J4:JR4)

= 1,094,640.12

Cell [JU4] ("Total Rebates") is this (array) formula:

=SUM(IF(MOD(COLUMN(K4:JS4)

= 1,094,640.12

As

[JT4]: =SUMIF(J1:JR1,"<>""",J4:JR

= 1,108,570.75

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

= 1,108,573.58

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

```
{=SUM(IF(ISNUMBER($J$3:$JR$3),J4:JR4),0)}
{=SUM(IF(ISNUMBER($K$3:$JS$3),0,K4:JS4))}
```

=SUM(IF(MOD(COLUMN(K4:JS4)

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

Assumed you knew, since the original formulas was array formulas.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Is =SUM(IF(MOD(COLUMN(K4:JS4)

and must be =SUM(IF(MOD(COLUMN(J4:JR4)

,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)

or =SUM(IF(MOD(COLUMN(K4:JS4)