< Go Back

Sum visible rows in a filtered list

Excel Sum formula to total a column, rows or only visible cells

Formula

=SUBTOTAL(9,range)

Explanation

When using Excel, if you apply a filter to a row and want to create a total number of rows for a particular property, you must apply a filter based on that property. This way, only those rows that contain that particular attribute are displayed. The SUBTOTAL function can then be used with the 9 or 109 function.

Example

The benefit of this function is that it does not consider the filtered rows and displays the sum of all of these rows after applying the filter.
Below is an example of a cell in column F using a series of cells from F5 to F9, which are the rows visible after applying the filter.
= SUBTOTAL(9,F5:F9)
If you manually hide the line (ie right click, hide), use this version instead:
We have already discussed the two functions 9 and 109 above. Now we will discuss function 9. When using formulas that must be applied to lines that have been manually hidden, use function 9. The syntax of this function is as follows:
= SUBTOTAL (109, F5: F9)
When the SUBTOTAL function is applied in this way, many other calculations can also be performed. Other calculations may include COUNT, SUM, MAX, MIN, etc.

Figure1. Example of Sum visible rows in a filtered list

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar