Tom’s Tutorials For Excel: Summing Every Nth Cell

Tom’s Tutorials For Excel: Summing Every Nth Cell

Here’s an array formula that allows you to sum every Nth cell, where “N” is the interval number between cells. In the pictured example, you see that every 7 cells are summed in the budget worksheet with the array formula
=SUM(IF(MOD(ROW(B7:B28),7)=0,B7:B28))

You are not limited to the SUM function. To average every 7 cells, modify the formula as
=AVERAGE(IF(MOD(ROW(B7:B28),7)=0,B7:B28))

Recall, an array formula is applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.
If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
26 comments on “Tom’s Tutorials For Excel: Summing Every Nth Cell
  1. Bob says:

    Thanks for publishing this information.I modified your formula to sum every other column. and it works great on my sample test.
    =SUM(IF(MOD(COLUMN(H10:L10),2)=0,H10:L10))
    But my sheet has about 3900 columns and even though I specify column I as the first column, it does not go back that far and gives the wrong result. Is there a limit to how many cells this formula can handle? I tried highlighting the characters within the SUM brackets, hit F9 and its says too many characters in the formula.

    • Tom Urtis says:

      I’m not aware of a limitation, but sometimes Microsoft surprises us.
      Can you give some sample data, where (which columns it is located), and what your expected are. There might be another way to go about this.

      • SUYOG SUBHASHCHANDRA DHOOT says:

        Hi Tom Sir I tried formula suggested by you for every nth row in excel sheet please find the same and please help me for sum of every 18th row of I1:I5253 cell .

        • Tom Urtis says:

          This would do that:
          =SUM(IF(MOD(ROW(I1:I5253),18)=0,I1:I5253))

          Remember, this is an array formula. You apply it to a cell with Ctrl+Shift+Enter not just with Enter.

  2. Bob says:

    Here is a row with the first few columns. In my actual sheet I have almost 4,000 columns.
    31.73264 117.0325 0 2007 72.3302 0.003095 21 0.2394868 22 0.2706011 23 0.292337 24 0.3291052 25 0.1734213

    The rest of the columns are like the last two in this example. The integer 25 is a site name and the real number 0.1734213 is one of the values I need to sum. So in this example I want to sum every other number starting with 0.2394868, 0.2706011, 0.292337, 0.3291052 and 0.1734213 and many, many more of them. My formula works, but not with the large number of columns

  3. Katherine says:

    Tom – new to arrays and pulling my hair out at the moment. My spreadsheet is almost identical to yours except I am summing into my top row, going from D12:D1000, for example. There are no values in cells D500:D1000 yet. Should that affect the formula and sum? Also, for each of the columns did you copy and paste the formula, or is there a better way to use the same formula in adjacent cells? This will help immensely once I conquer the formula and actually get the right answer! Thanks!

    • Tom Urtis says:

      I just looked at my original workbook here with that example to make sure, and if there are no values in some cells being summed, it will not affect the Totals formulas in your row 1 because it’s like any other sum or addition formula, where adding zero among the list of numbers being evaluated still results in the same result of all numbers adding up to whatever they add up to.

      I Ctrl+Shift+Entered the formula (as an array formula) into cell B30 and copied that into C30:F30, meaning it was only one time I typed in the formula.

      I think, but not exactly sure, depending on if I interpreted your comment properly. you are summing for totals in row 1, whereas my pictured example shows my totals being summed in row 30. Your comment and question might relate to how to structure the formula because you are using row 1 for the formulas but really your data starts in row 12. If you can tell me the row intervals being summed (it was 7 in my example), maybe I can help you with an answer. Or, maybe I don’t understand the problem, which if that’s the case, apologies in advance, please repost with where I’m not understanding what you need.

      Thanks for following my page.

      Tom

  4. Nicholas says:

    thanks for the tip Tom. is there a way where i can add another condition? id like to set when the begin and end points.

    So in your example above, if i set the begin to 7 and the end to 21, then id get the total of the first 3 of 4 subtotals (ie 5906 + 19092 +7509 for Qtr1).

    if i set the begin to 14 and end to 28, then id get the total of the last 3 of 4 subtotals ( 19092 +7509 + 6371 for Qtr1)

    and if i set the begin to 14 and the end to 27 then id get the total of the middle 2 subtotals ( 19092 + 7509)

    So basically, this new formula would be able to add every nth row between two points that you specify.

    thanks in advance!

  5. Rajesh Sinha says:

    Tom it’s a good trick,, but I’m unable to understand the relevance of it,, since every group has separate Total,, it’s not even Running Sum,, this can be performed just by using Sum or Average Group wise,, ☺

    • Tom Urtis says:

      The relevance is when the data you get is not as clean-looking and organized as the example I gave.

      Most data warehouses have a built-in method for exporting to Excel, or at the very least generating csv files. The actual output can be illogical, where the formula I posted can help. Example:


      x Column A Column B
      1 Employee Salary
      2 John Doe 65247
      3 Hired on 3-5-2011
      4 Birthday 2-6-1980
      5 Title ID 364879
      6
      7 Jane Moe 62148
      8 Hired on 7-8-2013
      9 Birthday 11-12-1989
      10 Title ID 35489
      11
      12 Mary Sue 58749
      13 Hired on 9-14-2005
      14 Birthday 10-9-1993
      15 Title ID 364879

      This is not an uncommon example. Here, you need to add up the salaries which are in column B every 5 rows. The data comes to you with unrelated numbers in column B due to an undesirable database design outside of your control, but your clients or workplace bosses won’t want excuses, they just want the correct numbers and they want them now. Such is business, but that’s what Excel is for.

  6. Frank says:

    Hello,

    In cell G2 I am trying to sum every 4 rows between G10:G34. The formula I have in G2 is: ={SUM(IF(MOD(ROW(G10:G34);4)=0;G10:G34))}. However, the result isn’t summing the values in G10, G14, G18, etc, it’s summing the values in G12, G16, G20… Have I entered the formula incorrectly? Thanks in advance for your help!

    PS I’m using Excel for Mac version 15.39.

    • Tom Urtis says:

      You can go about this one of three ways to have your cells of interest match up with the formula’s expectations.

      If you enter this formula in some cell…
      =MOD(ROW(G10:G34),4)
      …you get 2

      But if you modify the formula to get a zero in that segment, such as
      =MOD(ROW(G10:G34)+2,4)
      or
      =MOD(ROW(G10:G34)-2,4)
      or
      =MOD(ROW(G10:G34),4)=2

      …then you satisfy the =0 match requirement for rows of interest.

      Therefore, either of these array formulas work:
      =SUM(IF(MOD(ROW(G10:G34)+2,4)=0,G10:G34))
      =SUM(IF(MOD(ROW(G10:G34)-2,4)=0,G10:G34))
      =SUM(IF(MOD(ROW(G10:G34),4)=2,G10:G34))

      I used commas here, where you used semicolons, so adjust the formulas as need be.

  7. Adam says:

    can i do this, but leave 2 decimal places and dollar sign in output as well?

  8. Sam says:

    Hi.
    I tried the averaging with columns instead of rows. It seemed to almost work perfectly. The only issue is that I have a table which will be populated more and more over time. So a lot of the included cells are blank. So it is dividing the summed number of the populated cells by the number of overall cells, not just those which I have populated. Any ideas on how to fix this?

    • Tom Urtis says:

      Expanding the range and staying in column B for every 7 rows, this should work:
      =AVERAGE(IF(MOD(ROW(B7:B57)-ROW(B7),7)=0,IF(B7:B57<>0,B7:B57)))
      It’s an array formula, applied with Ctrl+Shift+Enter, not just with Enter.

  9. Peter Capener says:

    Hi
    This is really helpful. I have a large database of values for every half an hour over 6 months and I want to get an average for each half an hour. So I think I see how I can use your formula to get the average of each half hour over the 6 month period, but I then want to drag the formula down to get an average for each of the 48 half hours in the day. Can I drag the formula down? It doesn’t give meaningful results if I do that. And even if I then rewrite the formula for the next half hour with the changed ranges it doesn’t seem to work either. I think I must be doing something wrong…. Any suggestions gratefully received.
    Thanks very much
    Pete

    • Tom Urtis says:

      Hello Peter – –

      I’m not sure I completely follow your question, it terms of how your data is arranged. For example, this tutorial shows how to operate (sum in this case) every few cells, and you want to average cells in columns (presumably) maybe in 48 contiguous rows that each contain 30 minutes worth of data. On the face of it, your question seems do-able, but if you can provide an explanation of what ranges are involved, what data is in what cells, how many rows apart the data is, and what formula you mentioned that works for the first set of data, and so on, I or someone else reading this can offer a more meaningful and relevant solution. Thanks!

  10. Philip Harris says:

    The formula using MOD works nicely; however my data range is formula based. Some of the cells appear empty but actually have a formula resulting in a “” in that cell. The multiplication step within the array formula you’ve provided chokes on these “” in the array, resulting in those items becoming a #Value! error. Any suggestions for removing the “” elements…perhaps replacing them with 0’s?

    • Tom Urtis says:

      Both formulas contain MOD. Which formula (or is it both) that you see the #$VALUE! error, and, if by chance it is t5he AVERAGE formula, should the calculation treat those null cells as being zero, or should they be disregarded totally, which will affect the accuracy (or lack thereof) that you want.

  11. Angie says:

    I need the sum of columns, leaving every 1,2nd,3rd,…. cells
    I’m unable to figure out how to leave out the first cells, it always leaves out the last cells and sums it up.

    • Tom Urtis says:

      I am unable to understand what you are working with based just on those 2 short sentences. Try adding a little detail to what you are trying to do and what ranges you want to sum or not sum.

      • angie says:

        Apologies.
        I have a data set matrix of 156*156.
        I have a diagonal highlighted section starting from c3. I need to sum all rows for all 156 entries to the left of the diagonal and right of the diagonal. I’m using “=SUM(OFFSET(C3,0,0,1,FH3))” with the width in FH3 being 155. It returns the first n values, I understand. I’m not able to work around the part where I sum everything but the first value in the first row, everything but first two values in the second and so on..

        • Tom Urtis says:

          Still not clear.

          You wrote:
          “I have a diagonal highlighted section starting from c3. I need to sum all rows for all 156 entries to the left of the diagonal and right of the diagonal.”
          To me, that raises one confusion because “the” is a singular reference, meaning C3, D4, E5, and so on.
          Then you wrote:
          “I sum everything but the first value in the first row, everything but first two values in the second and so on.”
          That raises two more confusions:
          • How is “everything but first two values in the second” relevant whatsoever if the diagonal whatever-it-is starts in row 3 which (because 3 is larger than 2) could not be “the second”.
          • How can there be”everything but first two values” if the diagonal cells only reside in 1 cell in each row, meaning only 1 value and not two.

          Neither I nor anyone else except you has seen your worksheet, so your description needs to make consistent sense. At this point because I am tied up with my clients’ development projects, try posting your question on an excellent Excel forum that I also contribute to when I get the chance. It is at https://www.mrexcel.com/board/forums/excel-questions.10/ which is free and easy to register. There are always experts who are on line to help you faster than I can this week.

  12. Daisy Dunn says:

    I got this formula to work for me but I need to go one step further and I’m stuck. I am looking to average every other row in column S based on the contents of cells in row C. It seems I should be using some combination of =AVERAGEIF(C2:C75,”Apples”, S2:S75) and the above array =AVERAGE(IF(MOD(ROW(S1:S75),2)=0,S1:S75)), but I keep getting #VALUE! error. Any suggestions?

    • Tom Urtis says:

      The array formula (Ctrl+Shift+Enter) that you would need for that is
      =SUM(IF(MOD(ROW(S1:S75),2)=0,S1:S75))/SUMPRODUCT((C1:C75="Apples")+0,(ISEVEN(ROW(C1:C75)))+0)

Leave a Reply

Your email address will not be published. Required fields are marked *

*