Simple & Easy Excel Formula to Sum with Filters
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:
- 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.
- 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.
- 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.
- 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 thecriteria
.
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 inarray
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:
FILTER(C2:C10, B2:B10 = "January")
creates an array of values fromC2:C10
where the corresponding cell inB2:B10
is “January”.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:
- Identify the Range: Determine the range of data you want to sum and the criteria for filtering.
- Apply the FILTER Function: Use
FILTER
to create an array of values that meet your criteria. - Use SUM: Wrap the
FILTER
function insideSUM
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:
(B2:B10 = "January")
creates an array ofTRUE
andFALSE
values based on whether each cell inB2:B10
is “January”.(D2:D10 > 500)
creates another array ofTRUE
andFALSE
values based on whether each cell inD2:D10
is greater than 500.*
performs an element-wise AND operation on these arrays. The result isTRUE
only where both conditions are met.FILTER(C2:C10, ...)
then extracts the values fromC2:C10
where the corresponding element in the boolean array isTRUE
.- 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:
COUNTIF(TopCustomers[Customer], SalesData[Customer])
checks each value inSalesData[Customer]
against the list inTopCustomers[Customer]
. It returns an array of1
s for matches (i.e., top customers) and0
s for non-matches.FILTER(SalesData[Amount], ...)
then filters theAmount
column based on this array, keeping only the values corresponding to1
s.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
.
Frequently Asked Questions (FAQs)
Can I use SUM with FILTER in older versions of Excel?
The FILTER function is only available in Excel 365 and Excel 2019. For older versions, you can use SUMIFS or SUMPRODUCT for similar functionality.
How can I sum values based on multiple criteria without using FILTER?
You can use SUMIFS for multiple criteria:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
What should I do if my SUM with FILTER formula returns zero?
Check if your criteria are correct and if the FILTER function is returning the expected array. Also, ensure that the ranges are correctly aligned.
Can I use wildcards in SUMIF or SUMIFS?
Yes, you can use * (asterisk) to match any number of characters and ? (question mark) to match any single character:
=SUMIF(A2:A10, "Apple*", B2:B10)
How do I sum values in Excel based on a date range?
To sum sales between January 1, 2023, and March 31, 2023, use:
=SUM(FILTER(SalesData[Sales], (SalesData[Date] >= DATE(2023,1,1)) * (SalesData[Date] <= DATE(2023,3,31))))
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.