MOD function question

Hi All

I am currently working with a sample formula as shown below.

=SUM(IF(MOD(COLUMN($A$1:$O$1),2)=0,$A$1:$O$1,0))

If I set up numeric data in row 1 (i.e. different numbers in each column of row 1 from column A up to and including column O), the formula works fine in that it returns the sum of every second item on the row.

Here is the problem - I am trying to use this formula in a spreadsheet where i will have to add columns to the left of column A. (For things like desc or dept etc. The moment I add an odd number of columns, the formulas get skewed by the number of columns I insert before column A. (the function seems to be anchored on counting from column A). Is there a way to make this formula work where you want it to work? In my case. I want it to work at whatever column the numbers start on and then to end on column XFD. Thanks..

Maybe this:

=SUM(IF(MOD(COLUMN($B$1:$XFD$1),2)=IF(ISODD(COLUMN($B$1)),1,0),$B$1:$XFD$1,0))

Hi Glenn

Thanks for your answer. I am actually working with a count of every 5th column for my project. Columns that I add before column A could end up to be odd or even depending on what information I need to add. I could end up with 4,5,6,7,8,9 or more columns before my numbers kick in. Sorry for not clarifying this.

I would like this formula to be dynamic in that it automatically adjusts for however many columns i end up adding to the left of column A

Columns A, E, I or Columns A, F, K (before extra columns are inserted)?

Words may be ambiguous. Post a sample sheet showing EXACTLY which cells (from A1 to O1) you DO wish to sum.

Read the yellow banner (top of page) about sample sheets.

Umm. No. If you want every 5th column summed... show me a sample with every 5th column summed.

A guess:
=SUM(IF(MOD(COLUMN($A$1:$AFD$1)-COLUMN($A$1)+1,5)=1,$A$1:$AFD$1,0))

only NOW, do I know that you do not want to start with the first value...

=SUM(IF(MOD(COLUMN($A$2:$O$2)-COLUMN($A$2)+1,5)=0,$A$2:$O$2,0))

If needed, change the bit in red. If you do extend it to AFD - when you do not need to, you may notice an adverse effect on performance

Hi Glenn

Sorry for any confusion on my part but this is the first time i have used this function. I will study your example so as to educate myself. THANKS very much for your help.

The reason I wanted to extend to XFD was because I also add new number columns on the right. XFD, while extreme, would catch them. I'll see if I can find a workaround.

woo Hoo! We got there.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.