How to Sum Only Visible Cells in Excel

Sometimes, you may need to sum a range of cells that are filtered or hidden by some criteria. For example, you may want to calculate the total sales of a specific region or product. However, if you use the regular SUM function, it will include all the cells in the range, regardless of whether they are visible or not. This can lead to inaccurate results and confusion.

Fortunately, there are some ways to sum only visible cells in Excel, ignoring the filtered or hidden cells. In this article, we will show you four methods to do this:

• Using a table
• Using the SUBTOTAL function
• Using the AGGREGATE function
• Using a user-defined VBA function

Using a Table

One of the easiest ways to sum only visible cells in Excel is to use a table. A table is a special format that allows you to organize and analyze your data easily. You can create a table from your data by selecting it and pressing Ctrl + T or going to the Insert tab and clicking on Table.

One of the benefits of using a table is that it automatically adds a total row at the bottom, which shows the sum of each column. Moreover, if you filter or hide some rows in the table, the total row will adjust accordingly and show the sum of only the visible cells.

For example, suppose you have the following table that shows the sales data of some products in different regions:

To create a table from this data, select the range A1:C9 and press Ctrl + T. Then check the box that says “My table has headers” and click OK. You will see something like this:

Notice that the table has a total row that shows the sum of the sales column. Now, if you want to sum only the sales of the North region, you can filter the table by clicking on the drop-down arrow next to the Region header and selecting North. The table will look like this:

As you can see, the total row has changed to show the sum of only the visible cells in the sales column, which is 600. This is the result we want.

You can also hide some rows manually by selecting them and right-clicking and choosing Hide. The total row will also update to show the sum of only the visible cells.

Using the SUBTOTAL Function

Another way to sum only visible cells in Excel is to use the SUBTOTAL function. The SUBTOTAL function is a versatile function that can perform various calculations on a subset of data, such as sum, average, count, min, max, etc. It also has the ability to ignore filtered or hidden cells and only include visible cells in the calculation.

The syntax of the SUBTOTAL function is:

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

• function_num is a number that specifies which function to use. There are two sets of numbers for each function: one that includes hidden cells and one that excludes hidden cells. For example, 9 is for SUM (including hidden cells) and 109 is for SUM (excluding hidden cells). You can see the full list of function numbers in the Excel help or when you enter the SUBTOTAL function in a cell.
• ref1, ref2, … are the references to the cells or ranges that you want to calculate. You can enter up to 254 references, separated by commas.

To use the SUBTOTAL function to sum only visible cells, you need to use the function number 109, which is for SUM (excluding hidden cells). For example, if you want to sum the sales column in the previous table, you can enter the following formula in any cell (except the cells in the sales column):

=SUBTOTAL(109,C2:C9)

This will give you the same result as the total row in the table, which is 3600. However, if you filter or hide some rows in the table, the SUBTOTAL function will adjust and show the sum of only the visible cells. For example, if you filter the table by the North region, the SUBTOTAL function will return 600, which is the sum of the sales of the North region.

Using the AGGREGATE Function

The AGGREGATE function is similar to the SUBTOTAL function, but it has some additional features and options. It can perform various calculations on a subset of data, such as sum, average, count, min, max, etc. It also has the ability to ignore filtered or hidden cells and only include visible cells in the calculation. Moreover, it can also ignore errors or nested subtotals in the data.

The syntax of the AGGREGATE function is:

=AGGREGATE(function_num,options,ref1,[ref2],…)

• function_num is a number that specifies which function to use. There are 19 functions available, such as 1 for AVERAGE, 4 for MAX, 9 for SUM, etc. You can see the full list of function numbers in the Excel help or when you enter the AGGREGATE function in a cell.
• options is a number that specifies how to handle errors, hidden cells, and nested subtotals in the data. There are 6 options available, such as 0 for ignoring nothing, 5 for ignoring hidden cells, 6 for ignoring errors and hidden cells, etc. You can see the full list of options in the Excel help or when you enter the AGGREGATE function in a cell.
• ref1, ref2, … are the references to the cells or ranges that you want to calculate. You can enter up to 253 references, separated by commas.

To use the AGGREGATE function to sum only visible cells, you need to use the function number 9, which is for SUM, and the option 5, which is for ignoring hidden cells. For example, if you want to sum the sales column in the previous table, you can enter the following formula in any cell (except the cells in the sales column):

=AGGREGATE(9,5,C2:C9)

This will give you the same result as the SUBTOTAL function, which is 3600. However, if you filter or hide some rows in the table, the AGGREGATE function will adjust and show the sum of only the visible cells. For example, if you filter the table by the North region, the AGGREGATE function will return 600, which is the sum of the sales of the North region.

The advantage of using the AGGREGATE function over the SUBTOTAL function is that it can also handle errors in the data. For example, if you have some cells that contain errors, such as #DIV/0!, #N/A, #VALUE!, etc., the SUBTOTAL function will return an error as well. However, the AGGREGATE function can ignore the errors and still calculate the sum of the visible cells. To do this, you need to use the option 6, which is for ignoring errors and hidden cells. For example, if you have the following table that contains some errors in the sales column:

If you use the SUBTOTAL function to sum the sales column, you will get an error:

=SUBTOTAL(109,C2:C9)

#VALUE!

However, if you use the AGGREGATE function with the option 6, you will get the correct result:

=AGGREGATE(9,6,C2:C9)

1900

This is the sum of the visible cells that do not contain errors in the sales column.

Using a User-Defined VBA Function

The last method to sum only visible cells in Excel is to use a user-defined VBA function. VBA stands for Visual Basic for Applications, which is a programming language that allows you to create custom functions and macros in Excel. You can use VBA to create your own function that can sum only visible cells in a range.

To create a user-defined VBA function, you need to open the Visual Basic Editor by pressing Alt + F11 or going to the Developer tab and clicking on Visual Basic. Then, you need to insert a new module by going to the Insert menu and clicking on Module. In the module window, you need to type the following code:

Function SUMVISIBLE(rng As Range) As Double
' This function sums only the visible cells in a range
Dim cell As Range
Dim sum As Double
sum = 0
For Each cell In rng
    If cell.EntireRow.Hidden = False And cell.EntireColumn.Hidden = False Then
        sum = sum + cell.Value
    End If
Next cell
SUMVISIBLE = sum
End Function

This code defines a new function called SUMVISIBLE, which takes a range as an argument and returns the sum of only the visible cells in that range. It uses a loop to go through each cell in the range and check if it is hidden or not. If the cell is not hidden, it adds its value to the sum variable. Finally, it assigns the sum variable to the function name, which is the output of the function.

To use the SUMVISIBLE function in your worksheet, you can enter it in any cell (except the cells in the range) and refer to the range that you want to sum. For example, if you want to sum the sales column in the previous table, you can enter the following formula in any cell (except the cells in the sales column):

=SUMVISIBLE(C2:C9)

This will give you the same result as the other methods, which is 3600. However, if you filter or hide some rows in the table, the SUMVISIBLE function will adjust and show the sum of only the visible cells. For example, if you filter the table by the North region, the SUMVISIBLE function will return 600, which is the sum of the sales of the North region.

The advantage of using the SUMVISIBLE function over the other methods is that it gives you more control and flexibility over the calculation. You can modify the code to suit your needs and preferences. For example, you can change the function name, the argument name, the variable name, the condition, the operation, etc. You can also create other functions that perform different calculations on only visible cells, such as average, count, min, max, etc.

However, the drawback of using the SUMVISIBLE function is that it requires some knowledge and skills in VBA programming. It also may not work in some versions or settings of Excel that do not support macros or VBA. Moreover, it may not be compatible with other applications or platforms that use Excel files, such as Google Sheets or Power BI.