Sum every 10th cell in a row (Office XP)

Topic

New Reply

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.

Reply | Quote

Replies

How about

=SUMPRODUCT((MOD(COLUMN(E6:DS6),10)=5)*E6:DS6)

Reply | Quote

Hans,
It works..no surprise. Can you explain the =5? This is where my error was and I have re-read 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????

Reply | Quote

> 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

Reply | Quote

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

Reply | Quote