5 Ways To Sum Visible Cells In Excel

Summing visible cells in Excel can be a bit tricky, but there are several ways to do it. Whether you're working with filtered data or trying to sum only the cells that are currently visible on your screen, we've got you covered. In this article, we'll explore five different methods for summing visible cells in Excel.

Method 1: Using the SUBTOTAL Function

One of the most straightforward ways to sum visible cells in Excel is by using the SUBTOTAL function. This function ignores any hidden rows or columns and only includes the visible cells in the calculation. To use the SUBTOTAL function, follow these steps:

Select the cell where you want to display the sum.
Type "=SUBTOTAL(" and then select the function code for the calculation you want to perform (e.g., 109 for SUM).
Select the range of cells you want to sum.
Press Enter.

The SUBTOTAL function will automatically exclude any hidden cells from the calculation.

Method 2: Using the SUMIF Function

Another way to sum visible cells in Excel is by using the SUMIF function. This function allows you to sum a range of cells based on a specific condition, such as whether the cells are visible or not. To use the SUMIF function, follow these steps:

Select the cell where you want to display the sum.
Type "=SUMIF(" and then select the range of cells you want to sum.
Type "," and then select the range of cells that contains the values you want to check (e.g., the column that contains the data you want to sum).
Type "," and then enter the condition for the sum (e.g., ">0" to sum only visible cells).
Press Enter.

The SUMIF function will sum only the visible cells that meet the condition.

Method 3: Using VBA Macro

If you're comfortable with VBA programming, you can create a macro that sums visible cells in Excel. To create a macro, follow these steps:

Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
In the Visual Basic Editor, click Insert > Module to create a new module.
Paste the following code into the module:

Sub SumVisibleCells() Dim rng As Range Set rng = Selection MsgBox Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible)) End Sub

Save the module and return to Excel.
Select the range of cells you want to sum.
Press Alt + F8 to run the macro.

The macro will sum only the visible cells in the selected range.

Method 4: Using Power Query

If you're using Excel 2010 or later, you can use Power Query to sum visible cells. To use Power Query, follow these steps:

Select the range of cells you want to sum.
Go to the Data tab in the ribbon.
Click From Table/Range.
In the Power Query Editor, click Filter > Hide Blanks.
Click Add Column > Custom Column.
In the Custom Column formula, type "=SUM(VISIBLE)" and press Enter.

The Power Query will sum only the visible cells in the range.

Method 5: Using Filtered Sum Formula

Finally, you can use a filtered sum formula to sum visible cells in Excel. To use a filtered sum formula, follow these steps:

Select the cell where you want to display the sum.
Type "=SUM(" and then select the range of cells you want to sum.
Type "/FILTER(" and then select the range of cells that contains the values you want to check (e.g., the column that contains the data you want to sum).
Type "," and then enter the condition for the sum (e.g., ">0" to sum only visible cells).
Press Enter.

The filtered sum formula will sum only the visible cells that meet the condition.

Gallery of Sum Visible Cells in Excel

Sum Visible Cells in Excel Image Gallery

We hope these five methods for summing visible cells in Excel have been helpful. Whether you're a beginner or an advanced user, there's a method on this list that's sure to suit your needs. Do you have a favorite method for summing visible cells in Excel? Let us know in the comments!