Simple & Easy Excel Formula to Sum with Filters

Sharing is caring!

If you’re looking to sum values in Excel based on specific criteria, using a SUM with FILTER formula is your best bet. By the end of this detailed guide, you’ll know exactly how to create dynamic sums that only include data meeting your specified conditions.

We’ll cover everything from the basics of SUMIF and SUMIFS to advanced techniques like combining SUM with FILTER. So, Let’s get started!

What is the SUM with FILTER Formula in Excel?

The SUM with FILTER formula in Excel allows you to sum values in a range based on one or more conditions. This is particularly useful for large datasets where you need to calculate totals for specific subsets of data. The primary functions used for this purpose are SUMIF, SUMIFS, and the combination of SUM with FILTER.

Why Use SUM with FILTER?

There are several key benefits to using SUM with FILTER in Excel:

1. Flexibility: You can sum data based on virtually any criteria imaginable, from simple conditions like “greater than 500” to complex logical statements involving multiple columns and criteria.
2. Efficiency: Rather than manually sorting and filtering your data each time you need a sum, you can automate the process with a single formula. This saves time and reduces the risk of errors.
3. Dynamic Updating: When used in conjunction with Excel Tables or dynamic named ranges, your SUM with FILTER formulas will automatically adjust as new data is added or removed. This keeps your calculations always up-to-date.
4. Improved Insights: By easily calculating sums for different subsets of your data, you can uncover valuable insights and trends that might otherwise be hidden in the noise.

Now that we understand the power and potential of SUM with FILTER, let’s explore the various techniques for implementing it in Excel, starting with the foundational SUMIF and SUMIFS functions.

Using SUMIF to Sum with a Single Condition

What is SUMIF?

The SUMIF function sums the values in a range that meet a single criterion. It’s the simplest way to perform a conditional sum in Excel.

Syntax of SUMIF

Here’s the syntax for the SUMIF function:

SUMIF(range, criteria, [sum_range])

• range: The range of cells to evaluate against the criteria.
• criteria: The condition that must be met for a value to be included in the sum. This can be a number, text, a cell reference, or a logical expression.
• sum_range (optional): The actual cells to sum if different from the range being evaluated. If omitted, Excel will sum the cells in the range argument that meet the criteria.

Example of SUMIF

Let’s say you have a spreadsheet with sales data, and you want to sum all sales greater than $500. Your data is in the range B2:B10. Here’s how you would use SUMIF:

=SUMIF(B2:B10, ">500")

Excel will evaluate each cell in the range B2:B10, and if a cell’s value is greater than 500, it will be included in the sum.

SUMIF with Text Criteria

SUMIF isn’t limited to just numerical conditions. You can also use it to sum based on text criteria. For example, to sum all sales for the month of January (assuming the month is in column A):

=SUMIF(A2:A10, "January", B2:B10)

Here, we’re evaluating the range A2:A10 for the text “January”, and summing the corresponding values in B2:B10.

SUMIF with Wildcards

You can also use wildcards in your SUMIF criteria:

• * (asterisk) matches any number of characters
• ? (question mark) matches any single character

For instance, to sum all sales for Apple products (assuming the product name is in column A):

=SUMIF(A2:A10, "Apple*", B2:B10)

This will sum values in B2:B10 where the corresponding cell in A2:A10 starts with “Apple”.

Using SUMIFS for Multiple Criteria

What is SUMIFS?

The SUMIFS function is an extension of SUMIF that allows for multiple criteria. If you need to sum values based on two or more conditions, SUMIFS is the function you’ll want to use.

Syntax of SUMIFS

Here’s the syntax for the SUMIFS function:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range: The actual cells to sum. This is the first argument in SUMIFS (unlike SUMIF, where it’s the third argument).
• criteria_range1: The first range to evaluate against criteria1.
• criteria1: The condition that must be met in criteria_range1 for a value to be included in the sum.
• criteria_range2, criteria2, … (optional): Additional ranges and their associated criteria. You can include up to 127 range/criteria pairs.

Example of SUMIFS

Let’s expand on our previous example. Suppose you want to sum sales greater than $500 for the month of January. You would use SUMIFS like this:

=SUMIFS(C2:C10, B2:B10, "January", D2:D10, ">500")

Here’s how it breaks down:

• C2:C10 is the range to sum (the sales amounts).
• B2:B10 is the first criteria range (the month column), and “January” is the first criteria.
• D2:D10 is the second criteria range (the sales amount column), and “>500” is the second criteria.

Excel will sum only those values in C2:C10 where the corresponding cell in B2:B10 is “January” AND the corresponding cell in D2:D10 is greater than 500.

SUMIFS with Date Criteria

You can also use SUMIFS with date criteria. For example, to sum sales between January 1, 2023, and March 31, 2023 (assuming the date is in column A):

=SUMIFS(B2:B10, A2:A10, ">=1/1/2023", A2:A10, "<=3/31/2023")

This will sum values in B2:B10 where the corresponding date in A2:A10 is between January 1, 2023, and March 31, 2023, inclusive.

Combining SUM and FILTER Functions

What is the FILTER Function?

The FILTER function, available in Excel 365 and Excel 2019, allows you to extract an array of data that meets one or more conditions. It’s a powerful tool for working with large datasets.

Syntax of FILTER

Here’s the syntax for the FILTER function:

FILTER(array, include, [if_empty])

• array: The range or array to filter.
• include: A boolean array that determines which cells to include in the result. TRUE values cause the corresponding cell in array to be included.
• if_empty (optional): The value to return if no cells meet the criteria in include. If omitted, FILTER returns an empty array.

Example of Combining SUM and FILTER

Now let’s see how we can combine FILTER with SUM to perform conditional sums. Suppose you have sales data in columns A through C, with the month in column B. To sum the values from column C where the corresponding month in column B is “January”, you would use:

=SUM(FILTER(C2:C10, B2:B10 = "January"))

Here’s how it works:

1. FILTER(C2:C10, B2:B10 = "January") creates an array of values from C2:C10 where the corresponding cell in B2:B10 is “January”.
2. SUM() then sums this array.

The result is the sum of sales for the month of January.

Step-by-Step Guide for Using SUM with FILTER

Here’s a step-by-step process for using SUM with FILTER:

1. Identify the Range: Determine the range of data you want to sum and the criteria for filtering.
2. Apply the FILTER Function: Use FILTER to create an array of values that meet your criteria.
3. Use SUM: Wrap the FILTER function inside SUM to sum the filtered array.

Example

Let’s say you want to sum sales greater than $500 for the month of January. Your sales amounts are in C2:C10, the month is in B2:B10, and the criteria for the amount is in D2:D10. Here’s the formula:

=SUM(FILTER(C2:C10, (B2:B10 = "January") * (D2:D10 > 500)))

Here’s the breakdown:

1. (B2:B10 = "January") creates an array of TRUE and FALSE values based on whether each cell in B2:B10 is “January”.
2. (D2:D10 > 500) creates another array of TRUE and FALSE values based on whether each cell in D2:D10 is greater than 500.
3. * performs an element-wise AND operation on these arrays. The result is TRUE only where both conditions are met.
4. FILTER(C2:C10, ...) then extracts the values from C2:C10 where the corresponding element in the boolean array is TRUE.
5. Finally, SUM() sums the filtered values.

Advanced Usage: SUM with Multiple FILTER Conditions

Combining Conditions with Logical Operators

You can combine multiple conditions in your FILTER function using logical operators:

• * for AND
• + for OR

Example with AND Condition

Building on our previous example, let’s say you want to sum values in column C where column B is “January” AND column D is greater than $500. You would use:

=SUM(FILTER(C2:C10, (B2:B10 = "January") * (D2:D10 > 500)))

The * operator ensures that both conditions must be met for a value to be included in the sum.

Example with OR Condition

On the other hand, if you want to sum values in column C where column B is “January” OR column D is greater than $500, you would use:

=SUM(FILTER(C2:C10, (B2:B10 = "January") + (D2:D10 > 500)))

Here, the + operator includes a value in the sum if either condition is met.

Combining AND and OR Conditions

You can even mix and match AND and OR conditions. For example, to sum values where (column B is “January” AND column D is greater than $500) OR column E is “Yes”:

=SUM(FILTER(C2:C10, ((B2:B10 = "January") * (D2:D10 > 500)) + (E2:E10 = "Yes")))

The parentheses ensure that the AND condition is evaluated first, then the OR condition.

Practical Examples and Scenarios

Now that we’ve covered the various techniques for using SUM with FILTER in Excel, let’s look at some practical examples and scenarios where these formulas can be applied.

Example 1: Summing Sales Based on Date Range

Suppose you have a sales dataset with columns for Date, Amount, and various product categories. You want to sum the sales for a specific date range, say January 1, 2023, to March 31, 2023.

Assuming your data is in an Excel Table named SalesData, you can use the following formula:

=SUM(FILTER(SalesData[Amount], (SalesData[Date] >= DATE(2023,1,1)) * (SalesData[Date] <= DATE(2023,3,31))))

This will sum all values in the Amount column where the corresponding Date is between January 1, 2023, and March 31, 2023, inclusive.

Example 2: Summing Sales by Product Category

Let’s say you want to sum the sales for a specific product category, like “Electronics”.

Again, assuming your data is in the SalesData table, with a Category column:

=SUM(FILTER(SalesData[Amount], SalesData[Category] = "Electronics"))

This will sum all values in the Amount column where the corresponding Category is “Electronics”.

Example 3: Summing Sales with Multiple Conditions

You can also combine multiple conditions. For instance, to sum the sales for the “Electronics” category in the month of January:

=SUM(FILTER(SalesData[Amount], (SalesData[Category] = "Electronics") * (SalesData[Month] = "January")))

This sums values in Amount where Category is “Electronics” AND Month is “January”.

Example 4: Summing Sales for Top Customers

Suppose you want to sum the sales for your top 5 customers. You have a Customer column in your SalesData table, and a separate table named TopCustomers with a single column listing your top 5 customers.

You can use the following formula:

=SUM(FILTER(SalesData[Amount], COUNTIF(TopCustomers[Customer], SalesData[Customer])))

Here’s how it works:

1. COUNTIF(TopCustomers[Customer], SalesData[Customer]) checks each value in SalesData[Customer] against the list in TopCustomers[Customer]. It returns an array of 1s for matches (i.e., top customers) and 0s for non-matches.
2. FILTER(SalesData[Amount], ...) then filters the Amount column based on this array, keeping only the values corresponding to 1s.
3. SUM() sums these filtered values.

Example 5: Summing Sales by Region and Product

Let’s say your SalesData table has columns for Region, Product, and Amount. You want to sum the sales for a specific region and product combination, like “East” and “Widgets”.

You can use:

=SUM(FILTER(SalesData[Amount], (SalesData[Region] = "East") * (SalesData[Product] = "Widgets")))

This will sum values in Amount where Region is “East” AND Product is “Widgets”.

Common Errors and Troubleshooting

When working with SUM and FILTER formulas in Excel, you may encounter some common errors. Here’s how to recognize and resolve them.

#VALUE! Error

If you see a #VALUE! error, it typically means that the arrays or ranges in your formula are not of the same size. This often happens when the ranges in your FILTER function don’t align properly.

For example, if you have:

=SUM(FILTER(A2:A10, B2:B15 = "January"))

The ranges A2:A10 and B2:B15 are of different sizes, which will cause a #VALUE! error.

To fix this, make sure all your ranges have the same dimensions. In this case, you would change B2:B15 to B2:B10.

#CALC! Error

A #CALC! error usually indicates that your FILTER function is returning an empty array. This can happen if none of your data meets the criteria in your FILTER.

For example:

=SUM(FILTER(A2:A10, B2:B10 = "January"))

If none of the values in B2:B10 are “January”, FILTER will return an empty array, causing a #CALC! error when SUM tries to operate on it.

To avoid this, you can provide a default value for FILTER to return when the criteria isn’t met, using the if_empty argument:

=SUM(FILTER(A2:A10, B2:B10 = "January", 0))

Now, if FILTER returns an empty array, it will return 0 instead, and SUM will return 0 instead of an error.

Empty Results

If your SUM with FILTER formula is returning 0 or an empty cell, it likely means that no data is meeting your criteria. Double-check your conditions and ranges to ensure they are correct.

For example, if you have:

=SUM(FILTER(SalesData[Amount], SalesData[Month] = "Janyary"))

Note the typo in “Janyary”. Because no month will match this criteria, FILTER will return an empty array, and SUM will return 0.

Correct the spelling to “January” and the formula should work as expected.

#SPILL Errors

If you’re using Excel 365 or Excel 2019 and your FILTER function is returning a #SPILL error, it means the resulting array is too large to fit in the available space.

For example, if you have data in A2:A1000 and you use:

=FILTER(A2:A1000, A2:A1000 > 500)

in cell B2, but cells B3:B1000 are not empty, you will get a #SPILL error because the filtered array doesn’t have enough room to “spill” into the cells below B2.

To resolve this, either clear the cells below your formula, or use a dynamic array function like SPILL to spill the array into a different location:

=SPILL(FILTER(A2:A1000, A2:A1000 > 500), C2)

This will spill the filtered array starting at cell C2.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

=SUMIF(A2:A10, "Apple*", B2:B10)

=SUM(FILTER(SalesData[Sales], (SalesData[Date] >= DATE(2023,1,1)) * (SalesData[Date] <= DATE(2023,3,31))))

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.