Menu
I use the SUM(B1..B20) formula to sum a column, but when I filter the data by an adjacent column, the sum doesn't update to reflect only those rows not filtered out. How does one do this?
991
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
The SUBTOTAL function was designed to work with filtered data. It automatically ignores data in all filtered rows. It has this syntax: =SUBTOTAL(function_num, ref1, …)
You need to use the SUBTOTAL function. The SUBTOTAL function ignores rows that have been excluded by a filter.
The formula would look like this:
=SUBTOTAL(9,B1:B20) The function number 9, tells it to use the SUM function on the data range B1:B20.
If you are 'filtering' by hiding rows, the function number should be updated to 109.
=SUBTOTAL(109,B1:B20) The function number 109 is for the SUM function as well, but hidden rows are ignored.
188
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
