Do you want to sum up only the visible cells from a range?

Filtering data is a common task when working with large datasets in Google Sheets. When you apply a filter to a range of cells, you can quickly narrow down the data to show only the rows that meet specific criteria.

However, when you want to perform calculations on a filtered range, such as summing the visible cells, you may encounter some challenges.

That is because when you use the SUM function on a range in Google Sheets, it returns the sum of values in the range whether they’re hidden or visible.

If you want to take the sum of only cells that are visible based on certain filtering conditions, you will need to use another method.

This article will show you the ways in which you can take the sum of filtered or visible cells in Google Sheets.

Sum Only Visible Cells with the SUBTOTAL Function

The SUBTOTAL function is a powerful tool for performing calculations on filtered cells.

It enables you to not only sum filtered rows, but also carry out a range of other calculations such as average, minimum, maximum, count, product, standard deviation, and variance.

=SUBTOTAL(function_code, range1, [range2, …])

The SUBTOTAL function has three parameters.

• function_code specifies the type of calculation function to be performed on a range. As previously mentioned, the SUBTOTAL function can perform a variety of calculations beyond simple summation, such as multiplication and averaging, by leveraging other built-in functions like PRODUCT and AVERAGE. Rather than having to rewrite these functions, they have been assigned unique numbers. This allows you to specify the desired function by entering its corresponding number in the function_code parameter.

A list of numbers and their corresponding code is presented below.

• 1 is AVERAGE
• 2 is COUNT
• 3 is COUNTA
• 4 is MAX
• 5 is MIN
• 6 is PRODUCT
• 7 is STDEV
• 8 is STDEVP
• 9 is SUM
• 10 is VAR
• 11 is VARP

To skip hidden values for these codes, add a 10 before single-digit codes or a 1 before two-digit codes.

For example, 1 will give you the average of the range whether they’re hidden or not, but 101 will give you the average of only the visible cells. Likewise, 10 will give you the variance of the specified range while 110 will give you the variance of only the visible cells.

• range1 is the range of cells to include in the calculation.
• [range2, ...] is an optional argument that allows you to include additional ranges of cells in the calculation.

Before using the SUBTOTAL function to calculate the sum of visible cells, you need to add a filter to the range.

To add a filter to a range, select any cell in the dataset and click on the Filter icon in the toolbar menu.

Filter the Product Category column by clicking on the dropdown arrow in the column header and unchecking the Home & Garden and Electronics categories.

Click on OK when you’re done.

=SUBTOTAL(109, C4:C11)

After applying the filter, you can use the SUBTOTAL function to get the total Sales figure for the Clothing and Food & Beverages categories.

The SUBTOTAL function uses the 109 code to get the sum of only the visible cells in the range.

Sum Only Visible Cells with a Pivot Table

Pivot tables are powerful tools in Google Sheets that are very useful for summarizing and analyzing datasets. One of the benefits of using a pivot table is that it can easily handle and analyze filtered data, while still calculating the correct results based on the visible cells.

Follow these steps to use a pivot table to return the sum of filtered cells.

1. Select the range of cells that you want to summarize with a pivot table.

2. Go to the Insert menu tab and select Pivot table.

3. In the Create Pivot Table dialogue box, specify the location where you want to place the pivot table by clicking on the Existing sheet radio button. The Data range section will be auto-filled if you select the range before inserting the pivot table. Click Create when you’re done.

4. In the Pivot table editor panel on the right, drag the Sales (in USD) field to the Values section and the Product Category field to the Rows section.

5. Drag the Product category field to the Filters section.

Drag the Product Category field to the Filters section. Click on the Showing all items dropdown.

In the dropdown option, uncheck the Electronics and Home & Garden categories. Click on OK.

You should now see the pivot table, which summarizes the data based on the visible cells only. The values in the pivot table will update automatically based on any changes to the filter criteria.

Conclusions

When working with large datasets in Google Sheets, it is common to filter data to analyze specific subsets of the data. However, it can be challenging to perform calculations on the visible cells in a filtered range, as the hidden cells may affect the results.

The SUBTOTAL function and the pivot table are useful tools for summing only the visible cells in a filtered range.

These methods allow for more flexibility and customization when analyzing data in Google Sheets. By understanding and utilizing these techniques, you can easily calculate and analyze your data, regardless of the size or complexity of your dataset.

Do you think you will find this useful? Let me know in the comments section!