Sum Visible Cells Only in Excel

Introduction to Summing Visible Cells in Excel

When working with large datasets in Excel, it’s common to hide rows or columns to focus on specific data. However, this can cause issues when trying to sum up values, as Excel includes hidden cells in calculations by default. To sum visible cells only, you need to use specific functions or techniques. In this article, we’ll explore how to achieve this using various methods.

Using the SUBTOTAL Function

The SUBTOTAL function is a built-in Excel function that allows you to perform calculations on visible cells only. The syntax for the SUBTOTAL function is:

SUBTOTAL(function_num, ref1, [ref2], …)

Where:

• function_num is the number of the function to perform (e.g., 9 for SUM)
• ref1, ref2, etc. are the ranges of cells to include in the calculation

For example, to sum the values in the range A1:A10, excluding hidden cells, you would use the formula:

=SUBTOTAL(9, A1:A10)

This will return the sum of only the visible cells in the range.

Using the SUMVISIBLE Function (Excel 2013 and Later)

In Excel 2013 and later versions, you can use the SUMVISIBLE function, which is a part of the AGGREGATE function. The syntax for the SUMVISIBLE function is:

=AGGREGATE(2, 7, range)

Where:

• 2 is the code for the SUM function
• 7 is the code to ignore hidden rows and columns
• range is the range of cells to include in the calculation

For example, to sum the values in the range A1:A10, excluding hidden cells, you would use the formula:

=AGGREGATE(2, 7, A1:A10)

Using a User-Defined Function (UDF)

If you need to sum visible cells in an earlier version of Excel that doesn’t support the AGGREGATE function, you can create a user-defined function (UDF) using VBA. To do this:

• Open the Visual Basic Editor (VBE) by pressing Alt + F11 or navigating to Developer > Visual Basic
• In the VBE, click Insert > Module to insert a new module
• Paste the following code into the module:

  Function SumVisibleCells(range As Range) As Double
      Dim cell As Range
      For Each cell In range
          If cell.EntireRow.Hidden = False And cell.EntireColumn.Hidden = False Then
              SumVisibleCells = SumVisibleCells + cell.Value
          End If
      Next cell
  End Function
    

• Save the module by clicking File > Save (or press Ctrl + S)

You can then use the SumVisibleCells function in your worksheet like any other function:

=SumVisibleCells(A1:A10)

Using a PivotTable

Another way to sum visible cells is to use a PivotTable. To do this:

• Select the range of cells you want to sum, including headers
• Go to the Insert tab and click PivotTable
• Choose a cell to place the PivotTable and click OK
• Drag the field you want to sum to the Values area of the PivotTable
• Right-click on the field in the Values area and select Value Field Settings
• In the Value Field Settings dialog box, select the “Summarize by” dropdown and choose “Sum”
• Click OK to close the dialog box

The PivotTable will now show the sum of the visible cells.

Using a Formula with the FILTER Function (Excel 2021 and Later)

In Excel 2021 and later versions, you can use the FILTER function to sum visible cells. The syntax for the FILTER function is:

=SUM(FILTER(range, (range <> “”) * (NOT(ISBLANK(range))) * (NOT(ROW(range) = ROW(HIDDEN_ROWS())))))

Where:

• range is the range of cells to include in the calculation
• HIDDEN_ROWS() is a range of row numbers that are hidden

Note that this method requires you to define the HIDDEN_ROWS() range, which can be done using a named range or a dynamic array formula.

📝 Note: The methods described above assume that you are working with a range of cells that contains only numbers. If your range contains non-numeric data, you may need to modify the formulas or use additional functions to handle errors.

To illustrate the difference between the SUBTOTAL and AGGREGATE functions, consider the following table:

Values	Hidden
10	FALSE
20	TRUE
30	FALSE

If you use the SUBTOTAL function to sum the values, it will return 60 (10 + 30), ignoring the hidden row. If you use the AGGREGATE function, it will also return 60, but it will ignore both hidden rows and columns.

In summary, there are several ways to sum visible cells in Excel, including using the SUBTOTAL function, the AGGREGATE function, a user-defined function, a PivotTable, or a formula with the FILTER function. The method you choose will depend on your specific needs and the version of Excel you are using.