# How to Sum Every Nth Cell in Excel

Do you need to learn how to sum every nth cell in Excel? In this post, you will learn the best methods for summing up every nth cell value in Excel. Also, provide you VBA and Office Scripts code for dashboard or report automation projects. Let’s get started!

Excel does not have the direct formula to sum every nth cell, you have to use a combination of Excel functions, add support columns, or Loop through the range of cells using VBA or Office Scripts code.

You can use a combination of Excel basic functions **SUM**, **IF, MOD **and **ROW **to Sum every nth cell value in Excel or use the advanced Excel function** FILTER **if you deal with Office 365.

Excel **AUTOFILTER **with** SUBTOTAL **function or **Table **command helps you to Sum Every Nth cell in Excel.

Also, you have more options to Sum every Nth cell in Excel using **Power Query** or **Power Pivot**, which works for large or multiple data sets. You can write **VBA** or **Office Scripts** code to Sum every Nth cell in Excel for your automation, or dashboard projects.

In this example, you have received the Employee Details data in a CSV file. It has the Employee’s Name, Address, and Salary in three rows. To report the total salary, you need to sum every third record in the CSV data file. Sum the values in rows 3, 6, 9, … 30.

Get your copy of the example workbook used in this blog post to follow along!

## Sum Every Nth Cell in Excel with **Basic functions**

In this section, you learn how to use a combination of Excel basic functions **SUM**, **IF**, **MOD**, and **ROW** to Sum every Nth cell in Excel.

`SUM( number1, [number2], ... )`

**SUM **function adds all the given values or values in the cells

`IF( logical_test, value_if_true, [value_if_false] )`

**IF **function returns one value if the condition is true and another value if the condition is false

`MOD( number, divisor )`

**MOD **function divides the number by divisor and returns the remainder.

`ROW( [reference] )`

**ROW **function returns the row number of a reference. If the reference is not given it returns the current row number.

Go to cell **B1**, type the formula ** =SUM( IF( MOD( ROW( $A$1:$A$30 ), 3 ) = 0, $A$1:$A$30, 0 ) ) **and then Press

`Enter`.In the above formula, the **ROW** function returns the current row number, **MOD** function divides the row number by 3 since you need to sum the salary, which is in rows 3, 6, 9, … 30. **IF** function returns every third-row value and zero for all other rows. Finally, the **SUM** function adds the salary.

### Sum every 2nd ROW

Suppose the employee salary or the data you need to sum are in row 2. You just update the **MOD** function divisor argument 3 to 2.

`=SUM( IF( MOD( ROW( $A$1:$A$30 ), 2 ) = 0, $A$1:$A$30, 0 ) )`

### Sum every 3^{rd} Column

If the data are in columns, your excel range will be **A1:AD1**, and use the Column() function instead of Row() in the above formula.

`=SUM( IF( MOD( COLUMN( A1:AD1 ), 3 ) = 0, A1:AD1 , 0 ) ) `

## Sum Every Nth Cell with **SUMIF **or** SUMPRODUCT**

You can use either the **SUMIF **function or **SUMPRODUCT** function with the supported columns to Sum every Nth cell in Excel.

`SUMIF( range, criteria, [sum_range] )`

**SUMIF** function sums the values in a range that meet the specified condition

`SUMPRODUCT(array1, [array2], [array3], ...)`

**SUMPRODUCT** returns the sum of the products of corresponding arrays

Please follow the below steps to create support columns to Sum every nth cell in Excel.

First, you have to create a column for the row number using the **ROW **function and add another column using **IF** and **MOD** functions. Go to cell **B1**, type the formula ** =ROW(), **and then Press

**Enter.**Next type the formula

**in cell C1 and press**

`=IF( MOD( B1, 3 ) = 0, 1, 0 )`

**Drag the fill handle to the range**

`Enter`.**B1:C30**to copy both formulas.

**SUMIF** function to sum every 3^{rd} row

Go to cell **H1**, type the formula ** =SUMIF( C1:C30, 1, A1:A30 ), **and then Press

`Enter`.**SUMPRODUCT** function to sum every 3^{rd} row

Go to cell **H2**, type the formula ** =SUMPRODUCT( $A$1:$A$30, $C$1:$C$30 ) **and then Press

`Enter`.## Sum Every Nth Cell in Excel with **FILTER** function

This section explores Excel 365 **FILTER **function to Sum every Nth cell in Excel with supported columns. It allows you to filter a range of data based on criteria and will not work in the earlier versions of Excel.

`FILTER( array, include, [if_empty] )`

The **FILTER** function returns a filtered array of data

Go to cell **F1**, type the formula ** =SUM( FILTER( A1:A30, ( C1:C30 = 1 ) ) )**, and then Press

`Enter`.**FILTER** function filters the data in range A1:A30. It includes only the cell value 1 in the range C1:C30.

## Sum Every Nth Cell with **AUTOFILTER** and **SUBTOTAL**

Excel **AUTOFILTER** allows you to quickly filter data from large tables by applying criteria. you can use the **FILTER** and **SUBTOTAL** functions to Sum every Nth cell.

`SUBTOTAL( function_num, ref1, [ref2], ... )`

**SUBTOTAL **returns a subtotal in a list of visible cells.

Please follow the below steps to activate **AUTOFILTER**

- Select the range of cells
**A1:C30**, In the range of cells Column A has your data. B and C are for ROW and MOD functions. - To activate
**AUTOFILTER**either press the keyboard shortcut keys+`Ctrl`+`Shift`or Go to the`L`**Home**Tab and Select**Filter**in**Sort & Filter**command.

Once activated the AutoFilter you can see a filter button in each cell of the first row. Click the filter button in cell **C1**, Select item **1**, and press **OK**.

Go to cell **A32**, type the formula ** =SUBTOTAL( 9, A1:A30 ) )**, and then Press

**Number 9 in the formula refers**

`Enter`.**SUM**function.

## Sum Every Nth Cell in Excel with **TABLE**

In Excel, you can easily convert the range of cells into a Table. Select the range of cells that you need to include in the Table. Go to the Insert tab and click the Table button. Tables are useful for organizing and displaying data and it allows you to sort, filter, and display subtotals.

Please follow the below steps to add **TABLE** in Excel

- Add headers in Row 1 Employee Details, Row, and Mod in cells A1, B1, and C1 respectively.
- Update the formula
in column`=ROW() – 1`

**B**, Since you added a header row for creating a table. No changes in the MOD function in column C. - Select the range of cells
**A1:C31** - Press
or Click the`Ctrl`+`T`**Table**command in Insert Menu**.**

- Click the filter button in cell
**C1**, Select item**1**, and press**OK**

- Select any cell inside your data, Go to
**Table Design**Tab, and Check**Total Row.** - Once
**Total Row**is checked, you can see the total row in Row 32. Go to cell**A32**and Select the**Sum**function in the dropdown.

## Sum Every Nth Cell in Excel with **Power Pivot (DAX)**

**Power Pivot** is a data analysis tool in Excel. It allows you to create data models, import data, create calculations, and create interactive charts and visuals.

- Select the range of cells
**A1:C31,**Choose the command**Add to Data Model**in the**Power Pivot**Menu. It will add your data in Power Pivot and open the Power Pivot editor.

- Rename the PowerPivot Table name to
**PPData**, and the fourth column to**Only Salary**. - Type the formula
in the formula bar and Press`=IF( PPData[MOD] = 0, VALUE( PPData[Employee Details] ), 0 )`

`Enter`

- Select the
**Only Salary**column and Click**AutoSum.**Rename the measure name to**Total Salary.**

- Choose the
**Pivot Table**Icon in the**Home**Tab - Select
**Flattened PivotTable –**to open**Create Flattened PivotTable** - Choose
**Existing Worksheet**, and select cell**G1**in the Location Input box, where you need to place the data. - Press
**OK**

- The above steps add an empty pivot table to the PowerPivot worksheet cell
**G1**. Drag the measure**Total Salary**into the**Values**area in PivotTable Fields. You can notice the Total salary is in cell**G2**.

## Sum Every Nth Cell in Excel with **Power Query**

**Power Query** helps you to Extract, Transform, and Load data from multiple sources, including databases, files, web pages, and folders. Also, you can create format or process your data using Power Query GUI commands.

Steps to Sum Every 3^{rd} Cell in Excel using Power Query

- Select the range of cells
**A1:A31** - Choose
**From Table/Range**option in**Data**Menu– to open the Power Query window

- Go to
**Add Column**, and Choose the item**From 1**option in the**Index Column**, to add an Index column starting from 1.

- Click
**Custom Column**in the**Add Column**Tab to open the Custom Column dialog box.

- In the Custom Column dialog box, type the New Column Name
**Mod**, and type the formula`=Number.Mod( [Index], 3 )`

- Press
**OK.**

- Click the filter button in column
**Mod**, select item**0**, and press**OK.**

- Select the Column
**Details and choose**the**Sum**function in the**Statistics**command in**Transform**Tab.

- Choose the option
**Close & Load To…**in the**Close & Load**Menu in the**Home**Tab

- Choose the option
**Table**in the Select how you want to view this data in your workbook. - Select the
**Existing worksheet:**and choose the cell, where you need to put the data - Press
**OK**

You can see the **Total Salary** in cell D2.

## Sum Every Nth Cell in Excel with **VBA **Script

Excel **VBA** allows you to automate tasks, create macros, and manipulate data in a different way. You can create custom functions using Excel VBA. You use VBA **For each** loop and **IF** condition to sum every nth cell.

```
Sub SumNthCell()
' Declare variables
Dim wRange As Range
Dim rg As Range
Dim nCell As Integer
Dim totSalary As Double
' Initialize value
totSalary = 0
nCell = 3
' Assign selected range of cells
Set wRange = Sheets("VBA").Range(Selection.Address)
' Loop through the selected range
For Each rg In wRange
' check the nth Cell and Sum
If rg.Row Mod nCell = 0 Then totSalary = totSalary + rg.Value
Next
' Set the final value
Sheets("VBA").Range("D1").Value = totSalary
End Sub
```

Insert the above code into your **VBE **module.

To Run the **SumNthCell **macro

- Select the range of cells
**A2:A11**, and Press**Alt + F8**, the keyboard shortcut to open the Macro dialog box. - Choose the
**SumNthCell**Macro and then Press**Run.**

VBA code initializes the variables **totSalary** and **nCell**. Number 3 is assigned to the variable **nCell**. Because you need to sum the salary, which are in the 3^{rd} row. Assigns the selected range to the variable **wRange**. The code loop through the selected range, in each iteration, the macro checks the remainder value. If the remainder is Zero, then sum the current cell value with **totSalary**. Finally, the **totSalary** is put in cell **D1**.

## Sum Every Nth Cell in Excel with **Office Script**

**Office Scripts** works both desktop and web, used for creating and running macros in Excel. It allows you to record and automate repetitive tasks. You learn how to use the **For** loop to sum every nth cell in Excel.

```
function main(workbook: ExcelScript.Workbook) {
// Assign active worksheet to the variable
let currSheet = workbook.getActiveWorksheet()
// get selected range
let rng = workbook.getSelectedRange();
// Store Number of Rows
let rows = rng.getRowCount();
// Initialize nth value
let n = 3;
let total = 0;
// loop through the selected range of cells and step by n
for (let i = 2; i < rows; i += n) {
// Assign the cell value to the variable cellValue as string
let cellValue = rng.getCell(i, 0).getValue() as number;
// sum the value
total = total + cellValue
}
// Set output to the cell D1 in the active sheet
currSheet.getCell(0,3).setValue(total)
}
```

The script assigns the selected range to the variable **rng,** initializes the variables **total**,and **n**. Number 3 is assigned to the variable **n**. Because you need to sum the salary, which are in every 3^{rd} row in your data. The script loop through the selected range, you have used the step value n, which is 3. So, the for loop passes through every 3^{rd} cell and sums the cell value with the total. Finally, the **total** is set in cell **D1**.

Follow the below steps to execute the **Sum Nth Cell** Office scripts in your Excel

- Select the range of cells
**A1:A30** - Go to
**Automate**menu - Select the
**Sum Nth Cell**Script – to open the Code editor on the right side of the Excel window - Press
**Run**

**Conclusions**

Computing Sum, Average, Count, Max, and Min are common tasks in analysis. You have the direct formula to perform this common task. Sometimes you may ask to consider specific data for analysis instead of using all the data. In those scenarios, you need to use some complex functions or tools in Excel. You have learned a few different methods and have a better understanding of how to sum every nth cell in Excel.

Excel functions are dynamic and can update the value automatically when changes are made. In this tutorial, you learned how to use a combination of **SUM**, **IF**, **MOD**, and **ROW** Excel functions and the Excel 365 **FILTER** function to Sum every Nth cell.

You have more options to Sum every Nth cell in Excel using the **FILTER** command with the **SUBTOTAL** function and **TABLE** command.

Also, **Power Query** or **Power Pivot** methods are the best options when working with larger, external, or multiple data sources. Finally, **VBA** or **Office scripts** techniques are useful, if you have to Sum Every Nth Cell in your dashboard or report automaton.

Do you know any other methods that can be used to **Sum Every Nth Cell** in Excel? Let me know in the comments section!