< Go Back

Sum visible rows in a filtered list

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 functions. 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.

Example 

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

Leave a Comment

avatar