# 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 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:

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

creates an array of values from`C2:C10`

where the corresponding cell in`B2: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 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:

`(B2:B10 = "January")`

creates an array of`TRUE`

and`FALSE`

values based on whether each cell in`B2:B10`

is “January”.`(D2:D10 > 500)`

creates another array of`TRUE`

and`FALSE`

values based on whether each cell in`D2:D10`

is greater than 500.`*`

performs an element-wise AND operation on these arrays. The result is`TRUE`

only where both conditions are met.`FILTER(C2:C10, ...)`

then extracts the values from`C2:C10`

where the corresponding element in the boolean array is`TRUE`

.- 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 in`SalesData[Customer]`

against the list in`TopCustomers[Customer]`

. It returns an array of`1`

s for matches (i.e., top customers) and`0`

s for non-matches.`FILTER(SalesData[Amount], ...)`

then filters the`Amount`

column based on this array, keeping only the values corresponding to`1`

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.