Filter a list and calculate visible totals

In this lesson

Example

How it works

A filter hides rows that do not match; it leaves their data in place. Conditions in different columns combine: a row must have Team = Support and Amount > 50 to stay visible.

SUM covers all cells in its range, including filtered-out rows. SUBTOTAL with function number 109 sums visible rows. Label the two totals so readers know which view each describes.

Steps

1. 1

   Enter the list and put =SUM(C2:C6) in a labeled cell outside it. Check the full-source total of 290.

2. 2

   Select A1:C6 and choose Data > Filter, or convert the range to a table. Open the Team dropdown and keep only Support.

3. 3

   Open the Amount dropdown and choose Number Filters > Greater Than. Enter 50. The remaining IDs should be R03 and R05.

4. 4

   Enter the formula below in a separate cell labeled Visible total. It should show 150 while the SUM total stays 290.

   Excel argument separator

   Comma (,) Semicolon (;)

   =SUBTOTAL(109,C2:C6)

   Copy code

   English function names. Formula settings →

5. 5

   Check the row numbers and filter icons. Note the visible IDs R03 and R05 in your practice notes, together with the two conditions.

6. 6

   Use Data > Clear or the individual dropdowns to clear both filters. Count five records again and check that SUBTOTAL now shows 290.

Try it yourself

1. Apply Team equals Sales and Amount greater than or equal to 50. Predict the visible ID, its amount and both totals before checking the result.
2. Change R04 from 20 to 100 on a training copy. Reapply the filter if needed and explain why edited values may require a refresh of the existing filter view.

If something goes wrong

The visible total differs from the SUM total.

Check the active filters. In this example, 150 describes the Support-above-50 subset while 290 covers the full list. Label both scopes.

A row stays hidden after its amount is changed.

Reapply the filter. Check that the amount is numeric and that another column’s filter is not excluding the row.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

• Filter data in a range or table ↗