How to Use Excel SUMIFS with Multiple Criteria in Same Column

SUMIFS with multiple criteria in the same column refers to calculating the sum of values in a range based on more than one condition, with all conditions being applied to the same column or field within a dataset using the SUMIFS function in Excel.

In this Excel tutorial, you will learn 5 easy examples to use the SUMIFS function with multiple criteria in the same column.

In the below example, we have shown an easy example to give a basic idea of how we have used the SUMIFS function for multiple criteria in the same column.

To show examples of using the SUMIFS function for multiple criteria in the same column, we have used two/three criteria, used wildcards with criteria, and used dates as criteria.

⏷Problem with Excel SUMIFS Function
⏷SUM and SUMIFS Functions with Multiple Criteria
⏷SUM and SUMIFS Functions with 3 Criteria
⏷SUM and SUMIFS Functions with Wildcards
⏷SUMIFS Function with Multiple Date Criteria
  ⏵Using a Specific Date
  ⏵Using Today’s Date
⏷Multiple SUMIFS Functions for Multiple OR Criteria
⏷Alternative: Using Multiple SUMIF Functions

Problem with Excel SUMIFS Function While Using Multiple Criteria in the Same Range of a Column

First, have a look at the syntax of the SUMIFS function:

From the syntax, we can see that it should work for multiple criteria and criteria ranges.

Now let’s see a case, what happens if we apply the SUMIFS function for multiple criteria in the same range of a column? For that, we took a dataset that contains some electronic products’ ID, delivery status, and price.

• Insert the following formula in cell G9 and hit the Enter button to calculate the total price of the products with ‘Completed’ and ‘Pending’ status:

Unfortunately, it returned zero! Because the SUMIFS function only works for AND criteria with different columns, not for OR criteria in the same column. So as we applied the same criteria range separately, the SUMIFS function returned zero.

• Now take a look at another example, inserting the multiple criteria as an array using the following formula, the SUMIFS function returned the result separately as an array like the image below:

But we expect the output just in a cell.

In the below examples, we’ll see how we can overcome these scenarios.

Example 1. Combining SUM and SUMIFS Functions with Multiple Criteria in Same Column

In our very first example, we’ll combine the SUM and SUMIFS functions for multiple criteria in the same column. As we saw previously, the SUMIFS function returns the result separately as an array for array criteria. So we can use the SUM function before the SUMIFS function to sum the array output.

• Apply the following formula in cell G9 to get the total price:

• You can also use the SUMPRODUCT function instead of the SUM function, it will give you the same result.

• Have a look at the GIF file, the corresponding data in the dataset will be highlighted instantly whenever you change the criteria.

Note:

• We used conditional formatting to highlight different matched criteria.

• Also, we applied data validation rules to restrict the repeated entry of the criteria. Otherwise, the SUMIFS function will return the wrong result.

• Here’s the formula we used as validation criteria:

Read More: Excel SUMIFS with Multiple Vertical and Horizontal Criteria

Example 2. Joining SUM and SUMIFS Functions with 3 Criteria in Same Column

In this example, we’ll show how to combine the SUM and SUMIFS functions with 3 criteria in the same column. Just we’ll have to expand the array criteria in the SUMIFS function. For the below dataset, we’ll calculate the total sales for three states: Ohio, Nevada, and Florida.

• In cell G9, insert the following formula and press the Enter button:

• See the below GIF to get a complete overview.

Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns

Example 3. Combining SUM and SUMIFS Functions with Wildcards for Partial Match

In this example, we’ll use wildcard characters so that we can apply SUMIFS with partial matches for multiple criteria in the same column.

To show that, we took a new dataset that has different types of Fruits and their corresponding Prices. Look, some fruits have different types of species, such as Eureka Lemons, Bush Lemons, and Bearss Lemons. Now we’ll use a formula that will sum the prices if it gets the partial text: ‘Lemons’ and ‘Apples’ in any corresponding cell.

• Insert the following formula in cell E9 and hit the Enter button:

• Take a look at the following GIF, and how it recalculates and changes the highlighted cells whenever we change the criteria.

Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows

Example 4. Using SUMIFS Function with Multiple Date Criteria in Same Column

Now we’ll use the SUMIFS function with multiple date criteria in the same column. We’ll insert the date criteria in two ways: using any fixed date and using the TODAY function. So we modified the dataset by adding a Delivery Date column.

4.1 Using a Specific Date

Let, we want to calculate the delivered quantity within the previous 60 days from the date ‘2-Oct-23’ for the below dataset.

• For that, use the following formula in cell F15:

• If you update the date and number of days, the formula will show the quantity according to that. The output header will also be updated.

4.2 Using Today’s Date

If we use the TODAY function instead of the fixed date, then it will calculate the delivered quantity within the previous 30 days from today’s date.

• In cell F12, apply the following formula and press the Enter button:

• Today’s date is 30-Oct-23 while I am making this tutorial, so the formula is returning the delivered quantity within 30 days from 30-Oct-23.

• If you update the number of days, it will update the delivered quantity from today for that number of days.

Read More: Exclude Multiple Criteria in Same Column with SUMIFS Function

Example 5. Joining Multiple SUMIFS Functions for Multiple OR Criteria

Now we’ll show a different example compared to the previous examples. We’ll use multiple SUMIFS functions for two criteria in the same column and another criteria in another column.

For that, we added a new column in our previous dataset, which represents seller’s names. Our goal is to calculate the total delivered quantity for three sellers within a date range as mentioned in the image below.

• For that, we’ll need to use three SUMIFS functions for three different sellers. Insert the below formula in cell H12:

• After pressing the Enter button it will return the summed quantity for the date range.

Using Multiple SUMIF Functions Instead of Excel SUMIFS with Multiple Criteria in Same Column

Lastly, we’ll show an alternative approach. We’ll use multiple SUMIF functions instead of SUMIFS for multiple criteria in the same column in Excel. We’ll just have to use different SUMIF functions for individual criteria and then add them by using the plus sign or SUM function.

• Use the following formula in cell G9 to calculate the total price for pending and delivered items:

• Look, we got the same output as example 1.

Read More: SUMIFS: Sum Range Across Multiple Columns

Download Practice Workbook

We have shown 5 easy and useful examples of using SUMIFS with multiple criteria in the same column in Excel. Also, we covered an alternative method if you have trouble using the SUMIFS function. Besides using multiple criteria in the same column, we covered an example that includes another criterion from another column. Using the SUMIFS function with multiple criteria in the same column allows for precise and flexible data analysis in Excel. Thanks for reading the whole article and if you have any further queries or feedback you can inform us in the comment section.

Related Articles

• SUMIFS with Multiple Criteria Along Column and Row in Excel
  
• How to Use VBA SUMIFS with Multiple Criteria in Same Column

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel