Results 1 to 5 of 5

20081216, 03:18 #1
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Sum every 10th cell in a row (Office XP)
I need to sum every 10th cell in row 6 for the range E6 to DS6. I found several postings on how to sum every nth cell in a column, but nothing about every nth cell across a row. So far, all my attempts to modify the formulas to sum every nth cell in column do not produce the correct answer across a row. I am using the sumproduct and mod formulas...is this not correct to sum every 10th cell across a row? THANKS.

20081216, 03:25 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sum every 10th cell in a row (Office XP)
How about
=SUMPRODUCT((MOD(COLUMN(E6S6),10)=5)*E6S6)

20081216, 03:35 #3
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Sum every 10th cell in a row (Office XP)
Hans,
It works..no surprise. Can you explain the =5? This is where my error was and I have reread the help..but I still don't get how you selected 5 for this value. THANKS.
EDIT: Is the 5 derived because column E is really column 5 and this should be my remainder when performing the mod function????

20081216, 03:37 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sum every 10th cell in a row (Office XP)
> Is the 5 derived because column E is really column 5 and this should be my remainder when performing the mod function?
Yes indeed. If you had wanted to start from column H (the 8th column), you'd have used MOD(...,10)=8

20081216, 03:42 #5
 Join Date
 Mar 2001
 Location
 Canton, Ohio, USA
 Posts
 270
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Sum every 10th cell in a row (Office XP)
Wonderful.....thank you. I actually need 10 formulas like the one you provided and now I know the "secret"...thanks for sharing!!
I had spent more than hour trying to make it work using the help file...before my post. Too bad I didn't need to sum every nth cell in a column...I found that answer easily and I guess I thought it would be just as easy to convert this formula to a sum every nth cell in a row formula. I never really thought about the columns being "numbered" before. :)