Table of Contents
Calculating the sum of filtered rows in Excel can be achieved by following a simple process. First, select the data range that you want to filter. Then, apply the filter by selecting the Data tab and clicking on the Filter button. Once the data is filtered, you can select the filtered rows by clicking on the drop-down arrow in the column header and selecting “Filter by Selected Cells”. This will only display the filtered rows, making it easier to calculate the sum. Finally, use the SUM function to add up the values in the filtered rows. The result will be the sum of the filtered rows in the selected data range. This method allows for efficient and accurate calculation of the sum of filtered rows in Excel.
Sum Filtered Rows in Excel (With Example)
The easiest way to take the sum of a filtered range in Excel is to use the following syntax:
SUBTOTAL(109, A1:A10)
Note that the value 109 is a for taking the sum of a filtered range of rows.
The following example shows how to use this function in practice.
Example: Sum Filtered Rows in Excel
Suppose we have the following dataset that contains information about various basketball teams:
Next, let’s filter the data to only show the players on the Mavs or the Warriors.
To do so, highlight the cell range A1:B10. Then click the Data tab along the top ribbon and click the Filter button.
Then click the dropdown arrow next to Team and then uncheck the box next to Celtics, then click OK:
The data will automatically be filtered to remove rows with “Celtics” as the team:
If we attempt to use the SUM() function to sum the points column of the filtered rows, it will actually return the sum of all of the original values:
Instead, we can use the SUBTOTAL() function:
This function takes the sum of only the visible rows.
We can manually verify this by taking the sum of the visible rows:
Sum of Points in Visible Rows: 99 + 94 + 93 + 104 + 109 + 84 = 583.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I calculate the sum of filtered rows in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-filtered-rows-in-excel/
stats writer. "How can I calculate the sum of filtered rows in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-filtered-rows-in-excel/.
stats writer. "How can I calculate the sum of filtered rows in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-filtered-rows-in-excel/.
stats writer (2024) 'How can I calculate the sum of filtered rows in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-filtered-rows-in-excel/.
[1] stats writer, "How can I calculate the sum of filtered rows in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I calculate the sum of filtered rows in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.