How to Sum Every Nth Cell in Excel

ByJames Corrigan

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 3rd 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 =IF( MOD( B1, 3 ) = 0, 1, 0 ) in cell C1 and press Enter. Drag the fill handle to the range B1:C30 to copy both formulas.

SUMIF function to sum every 3rd row

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

SUMPRODUCT function to sum every 3rd 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

  1. 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.
  2. To activate AUTOFILTER either press the keyboard shortcut keys Ctrl + Shift + L or Go to the 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 Enter. Number 9 in the formula refers 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

  1. Add headers in Row 1 Employee Details, Row, and Mod in cells A1, B1, and C1 respectively.
  2. Update the formula =ROW() – 1 in column B, Since you added a header row for creating a table. No changes in the MOD function in column C.
  3. Select the range of cells A1:C31
  4. Press Ctrl + T or Click the Table command in Insert Menu.
  1. Click the filter button in cell C1, Select item 1, and press OK
  1. Select any cell inside your data, Go to Table Design Tab, and Check Total Row.
  2. 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.

  1. 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.
  1. Rename the PowerPivot Table name to PPData, and the fourth column to Only Salary.
  2. Type the formula =IF( PPData[MOD] = 0, VALUE( PPData[Employee Details] ), 0 ) in the formula bar and Press Enter
  1. Select the Only Salary column and Click AutoSum. Rename the measure name to Total Salary.
  1. Choose the Pivot Table Icon in the Home Tab
  2. Select Flattened PivotTable – to open Create Flattened PivotTable
  3. Choose Existing Worksheet, and select cell G1 in the Location Input box, where you need to place the data.
  4. Press OK
  1. 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 3rd Cell in Excel using Power Query

  1. Select the range of cells A1:A31
  2. Choose From Table/Range option in Data Menu– to open the Power Query window
  1. Go to Add Column, and Choose the item From 1 option in the Index Column, to add an Index column starting from 1.
  1. Click Custom Column in the Add Column Tab to open the Custom Column dialog box.

  1. In the Custom Column dialog box, type the New Column Name Mod, and type the formula =Number.Mod( [Index], 3 )
  2. Press OK.
  1. Click the filter button in column Mod, select item 0, and press OK.
  1. Select the Column Details and choose the Sum function in the Statistics command in Transform Tab.
  1. Choose the option Close & Load To… in the Close & Load Menu in the Home Tab
  1. Choose the option Table in the Select how you want to view this data in your workbook.
  2. Select the Existing worksheet: and choose the cell, where you need to put the data
  3. 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

  1. Select the range of cells A2:A11, and Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
  2. 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 3rd 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 3rd 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 3rd 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

  1. Select the range of cells A1:A30
  2. Go to Automate menu
  3. Select the Sum Nth Cell Script – to open the Code editor on the right side of the Excel window
  4. 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!

Similar Posts