Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Why doesn't my Excel total change when I filter the list to one session?

Technology category photographTechnology

I filtered our equipment list to Friday's session, but the SUM at the bottom still gives the quantity for the whole list. My colleague wants to delete the other sessions' rows so the number matches what we're looking at.

We need to keep the complete list. Microsoft documents SUBTOTAL for calculations that exclude filtered-out rows, with a further distinction between filtered rows and rows hidden manually. Microsoft's SUBTOTAL reference

How would you provide a clear total for the selected session without losing the overall total or the other sessions' records? The equipment desk needs to know what a number covers, not just see a number that changes.

Discussion

5 community replies

Keep the rows. Filtering changes what you see; the ordinary SUM still evaluates its referenced cells. The disagreement is about whether you want a total for the complete range or the filtered selection, and both numbers may be useful.

For a vertical quantity range, Microsoft's SUBTOTAL options 9 and 109 both sum while excluding filtered-out rows. They differ for rows hidden manually: 9 includes those, while 109 excludes them. Choose that behaviour deliberately rather than copying a formula number without its meaning.

Label the complete-list total separately from the filtered total. Someone printing the page or reading it after a filter change should be able to understand why the two numbers differ.

I wouldn't frame your colleague's suggestion as careless. They want the visible list and its total to agree. Explain that you can give them that view while retaining records needed for the other sessions.

Show the result with Friday selected and then with another session selected. That makes the distinction concrete. Also leave the scope visible on any exported desk sheet, because the person receiving it won't necessarily see the filter controls that produced it.

Join the discussion

More in Technology