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.
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.
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.
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 .
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.
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
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!
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
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!
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,, ☺
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.
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.
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.
can i do this, but leave 2 decimal places and dollar sign in output as well?
These formulas return values, so all you need to do is format the formula cells as you wish.
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?
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.
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
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!
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?
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.
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.
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.
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..
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.
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?
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)I’m writing the formula for adding every 24th value, within the range of 744. But it’s not working {=SUM(IF(MOD(ROW(A1:A744),24)=0,A1:A744))}.
Just saying it is not working tells me nothing. Are you getting an error? Is there a non-number, such as a letter or some character in a cell?
Your formula works for me, no problem, I just tested it OK.
You probably did not do what the instructions said, which is to apply the formula to a cell with Ctrl+Shift+Enter, not just with Enter.
Hi Tom,
Used your formula, it works a treat. Many Thanks
Cool — thanks for letting me know!
Hi Tom,
i am trying to find a solution to this problem.
In Sheet1, cell A1 i would like to the sum of Sheet2 A2:C4. Then In Sheet1, cell A2 i would like to the sum of Sheet2 A5:C7 and so on for 16 times.
Great if you find a solution to this.
Cheers
Sorry False. this is it. 🙂
In Sheet1, cell A1 i would like to the sum of Sheet2 A2:C4. Then In Sheet1, cell B1 i would like to the sum of Sheet2 A5:C7 and so on for 16 times. Great if you find a solution to this.
Cheers
If your worksheets are set up exactly as you say, then enter this into cell A1 of Sheet1 and copy down 15 cells or however many cells you need to:
=SUM(OFFSET(Sheet2!A2,(ROW()+1-ROW($A$2))*2,0,3,3))
Hi,
Thanks for a fast reply. Good start, but in cell Sheet1 B1 i get the sum from Sheet2 cells A3:C5 instead of A5:C7. Later in my sheet i have nonrelated data in cell $A$2. That might cause a problem.
Sorry, now i realize my problem, i mixed my rows and columns…
In Sheet1, cell A1 i would like to the sum of Sheet2 A2:C4. Then In Sheet1, cell B1 i would like to the sum of Sheet2 D2:F4 and so on for 16 times.
This is your 4th post and each time your ranges change. As I said in my first reply, the solution I posted worked with your 2nd message. At this point, go ahead and adjust the formula I posted, that does work with your 2nd scenario, and modify it for whatever arrangement of data you decide to settle on.
If I try to use the formula “=SUM(IF(MOD(ROW(B426:B444),2)=0,B426:B444))” I only get the following error “There is a problem with this formula.” Upon pressing ok its pointing to “,2”. If I change it to a semicolon, it is pointing to “0,B426” and so on.
Are you absolutely positively sure that you attempted to apply the formula by pressing Ctrl+Shift+Enter and not just with Enter.
Hey, I am pretty sure I tried it with ctrl shift enter. In 2019 I get this error. On 2010 it is working, but I had to change it to “=SUM(IF(MOD(ROW($A$1:$A$20),2)=0,$A$1:$A$20))”
Got it to work in 2019 like this: “=SUM(IF(MOD(ROW($A$1:$A$20);2)=0;$A$1:$A$20)) so with a semicolon instead of comma.
Hi there, I have a spreadsheet with rows E2:e126
I want to total the sum of E2+ every 5th row on the spreadsheet so (E7), E12 and etc.. what formula do I use? I’m having a hard time.. also I want this formula to keep working as the spreadsheet increases.
any help would be appreciated.
Just to be clear, would this be one (1) single formula, or would it be a series of formulas at each 5 row interval, maybe in column F.
Helpful Formula!! However, my case must be differ ever so slightly as I get a zero as a result where I have values in my range. I have a payroll spreadsheet I am trying to sum the total of overtime worked across all employees. In Column F, starting row 18 and repeating every 19 rows for 2,500 rows, I have each employee’s overtime total. I am trying to sum each overtime subtotal into a grand total. I have words in some cells in column F and I am not sure if those words are throwing this formula off since they are not number values?
Are you 100% sure that, as my directions said, you pressed Ctrl+Shift+Enter, NOT JUST Enter?