Sum, Count or Average FILTER Results in Excel

To sum FILTER results in Excel, wrap the FILTER formula in SUM: FILTER picks the matching values and SUM adds them up. The same idea works with COUNT, AVERAGE, MAX and MIN. This guide shows each one on a small sales table, how to avoid errors when nothing matches, and how to total rows you filtered with the filter buttons instead.

How to sum FILTER results with one formula#

Filter the column you want to add, then put SUM around it. The pattern is:

=SUM(FILTER(column_to_add, test))

FILTER needs Excel 2021, Excel 2024 or Microsoft 365. Inside SUM, the results don't spill: SUM receives the whole list and returns one number. The FILTER function guide and the SUM function guide cover each function on its own.

All the examples use this table in A1:D9:

To add the East sales:

=SUM(FILTER(D2:D9,B2:B9="East"))

FILTER returns 1200, 650, 900 and 1300. SUM adds them: 4,050. Notice the first argument is only the Sales column, D2:D9. If you filter the whole table A2:D9, SUM still works, but it's harder to read and easy to get wrong.

Count, average, max and min of FILTER results#

Swap SUM for another function to answer a different question about the same rows:

COUNT counts numbers, so use it on a number column like Sales. ROWS counts the rows FILTER returns from any range. Both are reliable when at least one row matches. The next section explains what happens when none do.

What happens when nothing matches?#

When no row passes the test, FILTER returns a #CALC! error, and SUM passes that error on. Try =SUM(FILTER(D2:D9,B2:B9="North")): there is no North region, so the cell shows #CALC!.

Fix it with FILTER's third argument, if_empty. For a sum, use 0:

=SUM(FILTER(D2:D9,B2:B9="North",0))

The result is 0. For counts, use an empty text "" with COUNT, which ignores text: =COUNT(FILTER(D2:D9,B2:B9="North","")) returns 0. Don't use ROWS or COUNTA here: they count the one cell holding the message or error and return 1. For an average, wrap the formula in IFERROR, because the average of nothing is a #DIV/0! error.

Sum with AND, OR and other tests#

FILTER's test can be any formula that returns TRUE or FALSE for each row, so it handles conditions that are hard with SUMIFS.

AND: multiply the tests. East laptops:

=SUM(FILTER(D2:D9,(B2:B9="East")*(C2:C9="Laptop")))

That returns 1200 + 1300 = 2,500.

OR: add the tests. East or West:

=SUM(FILTER(D2:D9,(B2:B9="East")+(B2:B9="West")))

That returns 4,050 + 800 + 1,100 = 5,950. With SUMIFS you'd need two SUMIFS added together.

Tests with functions: FILTER can use MONTH, YEAR, LEFT or SEARCH on the column. For example, ISNUMBER(SEARCH("lap",C2:C9)) matches any product containing "lap". The guides to FILTER with multiple criteria, FILTER by date and FILTER for text that contains a word show many more tests you can sum.

How to total a FILTER result already on the sheet#

If a FILTER formula already spills a table on the sheet, refer to its result with the spill range operator #. Say F2 holds =FILTER(D2:D9,B2:B9="East"). Then:

=SUM(F2#)

returns 4,050, and it stays right when the filtered list grows or shrinks. If the spill has several columns, pick one with INDEX. With =FILTER(A2:D9,B2:B9="East") in F2, =SUM(INDEX(F2#,,4)) adds the fourth column, Sales.

Show the matching rows and their total together#

A filtered list changes length, so a total typed under it can end up in the way of the spill. Put the total above the list instead. With the region to show in F1:

1. In F3, type =FILTER(A2:D9,B2:B9=F1,"No sales") to list the matching rows.
2. In I1, type =SUM(FILTER(D2:D9,B2:B9=F1,0)) for their total.

Type East in F1, and the list shows four rows with 4,050 above it. Type West, and it shrinks to two rows with 1,900. Nothing ever sits below the spill, so you never get a #SPILL! error, and a reader sees the detail and the answer at once.

How to sum rows filtered with the filter buttons#

If you filtered the table with the filter buttons (Data > Filter, or Ctrl+Shift+L; Mac: ⌘⇧F), SUM still adds every row, hidden or not. Use SUBTOTAL instead:

=SUBTOTAL(109,D2:D9)

The 109 means "SUM, ignoring hidden rows". Filter the Region column to East, and the formula shows 4,050. Clear the filter, and it shows the full total of 8,150. Use 103 to count visible non-empty cells and 101 for the average.

AGGREGATE does the same job with more options, such as ignoring errors: =AGGREGATE(9,5,D2:D9) sums visible rows. The SUM function article explains why a plain SUM sometimes gives a total you didn't expect.

SUM with FILTER or SUMIFS?#

For one or two simple conditions, =SUMIFS(D2:D9,B2:B9,"East") gives the same 4,050, works in every version and is shorter. Choose SUM with FILTER when you need OR logic, a test built with another function, or when you want to show the matching rows and their total side by side.

Practise it#

Totalling the rows that match a condition is one of the most common tasks in Excel tests. Practise FILTER in a live spreadsheet: you type the formula and it checks it instantly. Then practise SUM and AutoSum to get fast at totals. Start with the exercise on this page: one formula for East laptop sales.