Excel Sum formula to total a column, rows or only visible cells
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.
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.
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