Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Sum every nth cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum every nth cell

    Hi,

    I have a spreadsheet with values from D3 up to AB3 and I would like to sum every 4th cell beginning from D3 (D3,H3,L3 etc..).
    I've tried a lot of formulas including SUMPRODUCT, MOD, OFFSET but I always get errors and I believe the reason is because all my cells from D3 up to AB3 contain formulas.
    Some of the formulas return numbers and some of them return percentages.

    Your help would be much appreciated.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Sum every nth cell

    How about:

    =SUMPRODUCT((MOD(COLUMN(D3:AB3),4)=0)*(D3:AB3))
    Regards Dante Amor

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Sum every nth cell

    Welcome to the Board!

    Whether the values are hard-coded or returned by formulas makes no difference, as long as it is returning numeric values and not text.

    Try:
    Code:
    =SUMPRODUCT(--(MOD(COLUMN(D3:AB3),4)=0),D3:AB3)
    Note that percentages are really decimals, i.e. 90% actuallt is .90. So that is how those values will be included in your sum.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum every nth cell

    Thank you both for the replies.
    Both formulas return a circular reference on cells E3,F3, I3,J3, M3,N3 and so on.
    These cells contain a simple formula (for example the formula in cell E3 is =D3/$C$54 and the cell properties are set to percentage)

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Sum every nth cell

    If you ignore the warning, does it still return the correct value?
    What cell are you putting these formulas in?
    What is in cell C54?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum every nth cell

    If I delete the formulas in the cells where I get the circular reference then I get the correct value.
    I place the formula on C3 and dragging it down until cell C52.
    C54 is the sum(C3:C52)

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Sum every nth cell

    I place the formula on C3 and dragging it down until cell C52.
    C54 is the sum(C3:C52)
    If you are placing the formula in cell C3, why is C3 in your sum formula in cell C54?
    Since you are using C54 in your calculations in cells D3:AB3, that would, indeed, cause a circular reference. You are indirectly trying to use the value of C3 in the formula in cell C3.
    To me, using C3 in your SUM formula does not sound correct.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Sum every nth cell

    Quote Originally Posted by Savvasy View Post
    If I delete the formulas in the cells where I get the circular reference then I get the correct value.
    I place the formula on C3 and dragging it down until cell C52.
    C54 is the sum(C3:C52)
    Your problem with the circular reference is the sum in C54, you are adding cell C3, but C3 is the result of the sum of D3 to AB3. Then in cell E3 you have D3 / C54, but C54 still does not happen, because C3 also adds E3 (although under the conditions of sumproduct, E3 is excluded)
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum every nth cell

    D3 and every 4th cell has the sales of an item per month.
    The spreadsheet has 50 items so lets say for January I have 50 items with their sales from D3 until D52.
    On C3 i put your formula and dragging down until C52 and on C54 i do a sum of total sales of all items for all months
    On E3 I would like to get the % of the total sales so this is the reason I am using C3 in my sum formula.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Sum every nth cell

    Quote Originally Posted by Savvasy View Post
    D3 and every 4th cell has the sales of an item per month.
    The spreadsheet has 50 items so lets say for January I have 50 items with their sales from D3 until D52.
    On C3 i put your formula and dragging down until C52 and on C54 i do a sum of total sales of all items for all months
    On E3 I would like to get the % of the total sales so this is the reason I am using C3 in my sum formula.
    To avoid circular reference you must add cell by cell:

    Cell formula
    C3 =sum(D3,H3,L3 etc)
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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