Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    How about

    =SUMPRODUCT((MOD(COLUMN(E6S6),10)=5)*E6S6)

  3. #3
    3 Star Lounger
    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 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????

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  5. #5
    3 Star Lounger
    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. :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •