How to Sum Every Nth Row or Column in Google Sheets Using SUMIF

You should learn all the possible data manipulation techniques as it can come in handy at any time in the future. Here learn new tips for working with Google Doc Spreadsheet. With SUMIF function you can sum every nth row or column in Google Sheets.

The nth row can be like every third row, every fourth, fifth row or repetition of any specific row numbers.

Here, for example, I am going to sum every third row. But don’t worry. You can easily customize this formula to sum any nth occurrence.

As I’ve already mentioned above, for this purpose, I am using the popular Sumif function.

example to sum every third row in Google Sheets

The Formula to Sum Every Nth Row in Google Sheets

Here is the formula to sum every 3rd row in Google Sheets.

=sumif(ArrayFormula(mod((row(A2:A)-row(A2)+1),3)),0,A2:A)

You can tweak this formula easily to sum every custom number of rows like every 4th, 5th, 6th or repetition of any nth rows.

For example, if you want to sum every 5th row, please change the number 3 to 5 in the formula.

Formula Explanation

First, understand the formula arguments. That’s the easiest way to learn a formula.

The SUMIF Syntax:

=SUMIF(range, criterion, [sum_range])

See my formula:

=sumif(ArrayFormula(mod((row(A2:A)-row(A2)+1),3)),0,A2:A)

The range in this Sumif formula is the most important part of it. Actually, a MOD formula acts as the range in Sumif. So I should explain the MOD formula use.

The other part of the formula is like this. The value 0 at the end part is the criterion and the sum_rage is A2: A.

The MOD function Syntax:

MOD(dividend, divisor)

See this formula which is used as the range in the above Sumif formula:

=ArrayFormula(mod((row(A2:A)-row(A2)+1),3)

One Row formula took the place of the dividend in the MOD. Actually, the row formula returns the serial number 1, 2, 3 … that up to the last row in our data.

If you want to understand that you can follow my following tutorial – Auto Serial Numbering in Google Sheets Using Row Function.

To return the sequential numbers 1, 2, 3 … you only need to use row(A1:A) inside an ArrayFormula. But I have used the formula as row(A2:A)-row(A2)+1 inside the ArrayFormula as our data starts in row 2.

Please note that both the formula will return the same result and you can use either of them. If your data starts in row 7 the formula will be row(A7:A)-row(A7)+1.

The MOD function takes these serial numbers as the dividend and the number 3 is the divisor.

The MOD formula returns the value 0 in every third row as the divisor is 3. If you use the number 5 as the divisor, then the formula will return the value 0 in every fifth row.

In Sumif I’ve put the criterion as 0. So the Sumif formula will only sum the rows where the value in the range (you can say virtual MOD formula range) is 0. That’s all. This way you can Sum every nth Row in Google Sheets.

Sum Every Nth Column in Google Sheets (Additional Tips)

1. This formula won’t probably work in Excel as SUMIF behaves differently there.

Must Check: How Sumif Function Differed in Excel and Google Sheets.

2. You can sum or add every nth Column in Google Sheets as detailed above. I mean similar to adding nth rows.

How?

You can use the above same tips to sum every nth column in Google Sheets. But instead of the Row function to generate the serial numbers (relative row reference numbers), the same way you should use the Column function to generate relative column reference numbers.

19 COMMENTS

Homelader May 16, 2023 At 9:40 pm

Hi Prashanth,

I have a similar question: how can I sum within an interval of x cells?

Example:

Dragging =sum(A1:A6) gives me =sum(A2:A7). I’d like to have a way to drag and obtain =sum(A7:A13)

I have a set of daily data that I need to sum up weekly.

Thanks in advance.

- Prashanth May 17, 2023 At 4:51 pm
  
  Hi, Homelader,
  
  I don’t know how many weeks of data you have.
  
  For example, I assume you have 12 weeks of data.
  
  In cell C1, insert the following Sequence formula. It will return the multiplications of 7 in C1:C12.
  
  =sequence(12,1,1,7)
  
  In D1, you may insert the following formula and copy-paste it down.
  
  =sum(chooserows($A$1:$A,sequence(7,1,C1)))
  
  I’ll try to make it an array formula that runs without helper cells in the near future. I’ll try to share that tutorial with you then here.
  
Alexander Kolby October 17, 2022 At 5:10 pm

Hello Prashanth

I think this article is what comes closest to what I want to achieve. However, I can’t seem to make it work with what I got.

I want to sum every other row in an area if the text below it matches my reference.

- Prashanth October 20, 2022 At 3:38 pm
  
  Hi, Alexander Kolby,
  
  For this question, an example sheet would be easy for me to understand the data you want to process. You can leave the URL in the comment (I won’t make it public).
  
Angela August 3, 2022 At 2:05 am

Hi Prashanth,

Is it possible to SUMIF excluding every nth row? What would the formula look like?

-Angela

- Prashanth August 3, 2022 At 12:41 pm
  
  Hi, Angela,
  
  We can use SUMIFS.
  
  We can use the following formula if sum_range is B3:B and criteria_range1 is A2:A.
  
  =ArrayFormula(sumifs(B3:B,A3:A,"Apple", mod((row(A3:A)-row(A3)+1),16),">1"))
  
  Replace “Apple” with your criterion. The formula excludes every 16th and 17th row.
  
  Just for every 16th row, replace >1 with >0.
  
Cvan February 19, 2022 At 7:18 pm

Hey, I was wondering whether you can tell me how to sum x number of rows repeatedly where x can be variable?

For example, let’s say I have a row with numbers 1,2,5,4,2,8,5,7,8,4,9,6,3.

If I want to sum three numbers, I can put the sum formula against 5 with the cell range A1 to A3.

Then drag the ‘+’ to get the sum for A2:A4, A3:A5, etc.

However, I want to read the range length three from a different cell.

How would I do that? Thanks.

- Prashanth February 20, 2022 At 4:17 pm
  
  Hi, Cvan,
  
  Assume the values to sum are in A1:A and the variable (x) that determines the sum range is D2.
  
  Enter 3 in D2. Now instead of =sum(A1:A3), you can try =SUM(offset(A1,0,0,$D$2)).
  
  If the D2 value is 4, the above SUM and OFFSET combo will return the value that is equal to =sum(A1:A4).
  
Dan February 14, 2021 At 3:37 pm

Hi Prashanth,

Thank you for the great explanation!

I’m having trouble adapting the formula to achieve the following: I want to sum in every column (from A:E) only the values in the row “5M”… how could this be achieved?

# | A | B | C | D | E
EP | 1 | 3 | 5 | 6 | 3
5M | 3 | 5 | 6 | 8 | 8
Ko | 4 | 3 | 7 | 9 | 1
EP | 4 | 4 | 5 | 6 | 7
5M | 1 | 8 | 6 | 2 | 6
Ko | 1 | 3 | 5 | 6 | 3
EP | 3 | 5 | 6 | 8 | 8
5M | 4 | 3 | 7 | 9 | 1
Ko | 4 | 4 | 5 | 6 | 7

Thank you for a short reply.

Best regards
Dan

- Prashanth February 15, 2021 At 8:39 am
  
  Hi, Dan,
  
  When I enter your data in my Sheet, it occupies the range A1:F10.
  
  First, make B11:F11 empty. Then in cell B11, use the below Query or DSUM.
  
  QUERY:
  
  =query(A1:F10,"Select Sum(B),Sum(C),Sum(D),Sum(E),Sum(F) where A='5M' label Sum(B)'',Sum(C)'',Sum(D)'',Sum(E)'',Sum(F)''",1)
  
  DSUM:
  
  =ArrayFormula(dsum(A1:F11,B1:F1,{"#";"5M"}))
  
  When you use this formula, please make sure to change the “#” inside the formula with the actual value (field label) in cell A1. Because database functions are very sensitive to field labels.
  
  - Dan February 18, 2021 At 2:35 am
    
    Thank you very much! Good solutions!
    
    Meanwhile, I also found one solution:
    
    =sum(FILTER(B1:F11;mod(row(B1:F11);3)=0))
    
    whereas, the “3” ist the n-th row you want to sum up.
    
    Have a great day!
    Dan
    
Kari January 13, 2021 At 10:17 pm

I wish you could have an example for columns that are as detailed as this example is for rows.

- Prashanth January 14, 2021 At 8:39 am
  
  Hi, Kari,
  
  Thanks for your feedback.
  
Mairus August 5, 2019 At 2:57 pm

Hey,

I’m pasting this formula:

=sumif(ArrayFormula(mod((row(A12:A)-row(A12)+1);3));0;A12:A)

I use ; instead of , because of my Spreadsheet settings. But I’m getting an alert message that says: “Error Reference does not exist.”

Do you know why could be?

- Prashanth August 6, 2019 At 6:39 am
  
  Hi, Mairus,
  
  The formula seems perfect to me. It did work in my test.
  
  You can use this formula in any column other than column A to sum every 3rd occurrences. It will possibly sum the values in cell A14, A17, A20…
  
  If possible, replicate the error in a demo sheet and share it with me. So that I can find out the cause.
  
  You can read this tutorial to know some of the errors in Sheets and reasons – Different Error Types in Google Sheets and How to Correct It.
  
  Best,
  
Gabe June 6, 2019 At 11:20 pm

Hi Prashanth,

Is it possible to average every nth column, similar to the image you showed in the Additional Tips section?

Note: The provided link has been removed by the admin.

For example, in cell B3 I’d like to average all of the Data 1 columns in each row (so E3, I3, M3,, Q3, etc…), and do the same for C3 with Data 2 and D3 with Data 3

- Prashanth June 7, 2019 At 5:54 pm
  
  Hi, Gabe,
  
  Use the below formulas and copy (drag) down.
  
  Cell B3:
  
  =averageif(ArrayFormula(mod((column($E$1:$T$1)-COLUMN($E$1)+1),4)),1,E3:T3)
  
  Cell C3:
  
  =averageif(ArrayFormula(mod((column($E$1:$T$1)-COLUMN($E$1)+1),4)),2,E3:T3)
  
  Cell D3:
  
  =averageif(ArrayFormula(mod((column($E$1:$T$1)-COLUMN($E$1)+1),4)),3,E3:T3)
  
  I may write a detailed tutorial on this and link you back later.
  
  Best,
  
Sudinea February 4, 2019 At 8:08 pm

Hi, I tried to edit your formula but I keep getting an error. Here is an example of my table.
“https://docs.google.com/spreadsheets/d/1XdJd9BP8QIy_anLeroIFZCriLwUcxM0EjiJsc5E0Wb0/edit?usp=sharing”

I would like to sum Data5, every 10th row. Could you help me?

- Prashanth February 5, 2019 At 9:46 am
  
  Hi, Sudinea,
  
  Please note that as per your Google Sheets File > Spreadsheet settings, you must use a semi-colon instead of a comma in your formulas.
  
  How to Change a Non-Regional Google Sheets Formula.
  
  So the formula would be as follows.
  
  =sumif(ArrayFormula(mod((row(A6:A)-row(A6)+1);10));0;B1:B)
  
  But this simple SUMIF formula would also work.
  
  =sumif(A6:B64;"Data5";B6:B64)
  
  Sum every nth row but the first row is not the nth row.
  
  Example:
  
  If you want to sum every 10th rows but starting from the 11nth row, then tweak the formula as below.
  
  =sumif(ArrayFormula(mod((row(A6:A)-row(A6)+1);10));0;B2:B)
  
  Best,