How to sum only visible cells in Microsoft Excel

Microsoft Excel is a powerful spreadsheet software developed by Microsoft Corporation. It allows users to organize, analyze, and manipulate data in a graphical user interface. The software provides various tools and functions for performing mathematical calculations, creating charts and graphs, and designing complex formulas. With its intuitive and user-friendly interface, Excel is widely used by businesses, educational institutions, and individuals for various purposes, including financial analysis, budgeting, record keeping, and data visualization.

In this tutorial we will learn how to sum only visible cells in Microsoft Excel.Microsoft allows us to make calculations ignoring the filtered rows .To sum only visible cells we can use the Subtotal formula or the Aggregate function.  In this tutorial, we’ll use a dataset which has already filtered data and only a few rows are visible from the complete dataset.

Method 1 : Sum visible cells using the Aggregate function

Step 1 – Select a Blank Cell

• Select a blank cell where you want to sum the visible cells.

Step 2 – Place an Equals Sign

• Place an equals sign (=) in the targeted blank cell.

Step 3 – Enter the Aggregate Function.

• Enter the Aggregate function in the cell right next to the equals sign.

Step 4 – Place a Parenthesis and Select the Sum Option

•  Place a parenthesis.
• A Pop up menu will appear.
• Double-click on the SUM option, which is represented by the digit 9 in the formula.

Step 5 – Insert a comma and Select the “Ignore hidden rows” option

•  Place a comma.
• A Pop up menu will appear.
• Double-click on the “Ignore hidden rows”  option, which is represented by digit 5.

Step 6 – Place another comma and Enter the range

• Place a comma and enter the range of cells you want to sum.

Step 7 – Press the Enter Key

• Press the Enter key to get the sum of visible cells.

Method 2 : Sum visible cells using the Subtotal formula

Step 1 – Select a Blank Cell

• Select a blank cell where you want to sum the visible cells.

Step 2 – Place an Equals Sign

• Place an equals sign (=) in the targeted blank cell.

Step 3 – Use the Subtotal Formula

• Enter the Subtotal formula. 
• The syntax of subtotal formula is

                            subtotal(9,D2:D8)

• Where 9 will remain constant and D2:D8 is the range of the cells containing visible and invisible cells.

Step 4 – Press the Enter Key

• Press the Enter key to get the sum of visible cells.