SUMIFS with Multiple Criteria in Different Columns in Excel

One of the most common tasks in Excel is to perform sum operations. We can easily sum values by inserting them or their cell references in the SUM function argument. But It can get quite tiring to manually input values especially when we are working with large datasets. Again, we often have to conditionally sum values. We can carry out such calculations by joining the SUM and IF functions. To make it more convenient, Excel offers SUMIF and SUMIFS functions. However, the SUMIF function cannot take more than one criterion. That’s where the SUMIFS function comes to the rescue. This article will show you how you can use the SUMIFS function with Multiple Criteria in Different columns.

The SUMIFS function can take multiple criteria/range pairs. It’s not necessary to have those criteria in the same column. We can easily perform the AND, OR, AND+OR operations with the SUMIFS function. This article will cover all of those cases with a practical dataset. We’ll also show how you can sum values from multiple columns based on conditions. So, follow along with the article.

sumifs multiple criteria different columns

The above image shows the overview of the use of the SUMIFS function for both the AND condition and the OR condition. The mentioned two criteria are present in two different columns.


📁 Download Excel File

You can download this workbook to practice.


Introduction to SUMIFS Function in Excel

  • Objective:

Sum the cells from a range based on certain criteria.

  • Arguments & Explanation:
Arguments Required/Optional Explanation
sum_range Required The range of cells for summing.
criteria_range1 Required The first criteria range for testing the criterion.
criteria1 Required The first condition based on which values will be selected.
[criteria_range2] Optional The second criteria range for testing the criterion.
[criteria2] Optional The second criterion.

overview image of the SUMIFS function

The above image shows the SUMIFS function performing AND operation with 2 criteria from 2 different criteria ranges.


SUMIFS Multiple Criteria Different Columns: 4 Examples

The SUMIFS function can satisfy various operations and can also take up logical operators, dates, etc. in its argument. You’ll learn how to sum values from a single column as well as multiple columns based on conditions.


example

1. Sum Single Column for AND Criteria in Different Columns

Our first example will show the AND operation. In this example, values from a single column are added only when multiple criteria are satisfied simultaneously.

Case 1

Case 1: SUMIFS with Logical Operator

Quarter1 sales for Shirt only where the quantity is greater than 100

The SUMIFS function can have logical operators in its argument. In the above image, we show you the sum of Quarter1 sales for the product Shirt and the quantity greater than 100. For that purpose, we use the formula:

=SUMIFS(E5:E14, B5:B14, J4, D5:D14, J5)


Case 2

Case 2: Input Date Value in SUMIFS Argument

Quarter2 sales for Shirt ordered after 15 Jan

Similar to the logical operators, we can also input date values in the SUMIFS argument. The figure shows the sum of Quarter2 sales for the product Shirt ordered only after 15 Jan. The following formula brings out the result:

=SUMIFS(F5:F14, B5:B14, J4, G5:G14, “>”&J5)


Case 3

Case 3: Sum for Blank Cells

Quarter1 sales for Shirt where color is not mentioned

To use the AND operation where one criterion is blank, you’ve to insert “=” as criteria in the function argument. Here, we show the sum of Qurater1 sales for Shirts with unspecified colors. The formula is as follows:

=SUMIFS(E5:E14, B5:B14, J4, C5:C14, “=”)


example

2. Use SUMIFS to Sum Multiple Columns with Criteria in Different Columns

Sum of Quarter1 and Quarter2 sales for black color shirts

In the previous example, we have shown how you can sum values from a single column. Now, this example will cover multiple columns, for instance, 2 columns. You cannot input both the column range in a single SUMIFS argument. You have to add multiple SUMIFS by using the + operator or combine SUMIFS with the SUM function. The above image shows the sum of values from the Quarter1 and Quarter2 columns for Black color shirts. You can use either of the formulae below:

=SUMIFS(E5:E14,B5:B14,C16,C5:C14,C17)+SUMIFS(F5:F14,B5:B14,C16,C5:C14,C17)

Or,

=SUM(SUMIFS(E5:E14,B5:B14,C16,C5:C14,C17),SUMIFS(F5:F14,B5:B14,C16,C5:C14,C17))

Alternative: Array Formula to Sum Multiple Columns for Criteria in Different Columns

Sum of Quarter1 and Quarter2 sales using array formula

While we can get the job done with the SUMIFS function, it’s of no denial that the formula gets pretty long. We can create an array formula with either the SUM function or the SUMPRODUCT function to sum multiple columns which is much simpler. The formulae are:

=SUM((E5:F14) * (–(B5:B14=C16)) * (–(C5:C14=C17)))

Or,

=SUMPRODUCT((E5:F14) * (B5:B14=C16) * (C5:C14=C17))


example

3. Apply AND+OR Criteria Through SUMIFS

Total Quarter2 sales for either White Shirt or Green Shirt

With SUMIFS, we can also test AND+OR criteria. Here, we show you the total Quarter2 sales for the product Shirt, and the color can be White or Green. You can use multiple SUMIFS or combine SUMIFS with SUM to get the same outcome. The formulae used in the above image are:

=SUMIFS(F5:F14, B5:B14, C16, C5:C14, C17)+SUMIFS(F5:F14, B5:B14, C16, C5:C14, D17)

Or,

=SUM(SUMIFS(F5:F14, B5:B14, C16, C5:C14, C17:D17))


example

4. Apply SUMIFS for OR Criteria in Different Columns

Quarter1 sales for either Shirt or quantity greater than 100

The above image performs the OR operation. You’ll see the total of Quarter1 sales for the Product Shirt or where the Quantity is greater than 100. We used the formula:

=SUMIFS(E5:E14, B5:B14, J4) + SUMIFS(E5:E14, D5:D14, J5) – SUMIFS(E5:E14, B5:B14, J4, D5:D14, J5)

Alternative to SUMIFS for OR Criteria in Different Columns:

 Quarter1 sales for either Shirt or products of color Black

Testing OR condition with SUMIFS can result in the long formula. Easy alternatives are to use the array formula of the SUM function or the combination of SUM and FILTER functions. We used both formulas to get the same output. Here, we calculate the Quarter1 sales for the product Shirt or the product color Black.

=SUM(E5:E14*((B5:B14=C16)+(C5:C14=C17)), -E5:E14*((B5:B14=C16)*(C5:C14=C17)))

Or,

=SUM(FILTER(E5:E14, ((B5:B14=C16) + (C5:C14=C17))))

🔨 Formula Breakdown

👉 (C5:C14=C17)

This part of the formula tests Color column for Black.

👉 B5:B14=C16

This part tests Product column for Shirt.

👉 FILTER(E5:E14, ((B5:B14=C16) + (C5:C14=C17))

The FILTER function matches only the TRUE value

👉 SUM(FILTER(E5:E14, ((B5:B14=C16) + (C5:C14=C17))))

Finally, the SUM function gives final output is $34,000.


SUMIFS Alternative: SUMPRODUCT Function

Quarter2 sales for either White Shirt or Green Shirt using SUMPRDUCT

In the above image we show the SUMPRODUCT function as an alternative to SUMIFS to perform the AND+OR operation by calculating the total Quarter2 sales for either White Shirt or Green Shirt. The formula is:

=SUMPRODUCT(–(B5:B14=J4), –(ISNUMBER(MATCH(C5:C14,J5:J6,0))),F5:F14)

🔨 Formula Breakdown

👉 MATCH(C5:C14,J5:J6,0)

The MATCH function will match the color criteria and it will return the serial match number for the matched color and #N/A error for the unmatched.

👉 ISNUMBER(MATCH(C5:C14,J5:J6,0))

The ISNUMBER function will return TRUE for the numbers and FALSE for the errors.

👉 SUMPRODUCT(–(B5:B14=J4), –(ISNUMBER(MATCH(C5:C14,J5:J6,0))),F5:F14)

Lastly, the SUMPRODUCT function will sum the Quarter2 sales corresponding to TRUE outputs and Shirt as product.


How to Use SUMIFS to Sum for Multiple Criteria in Same Column in Excel

Quarter1 sales for either Jeans or Shorts

We can also use SUMIFS for multiple criteria from the same column. The above image shows the total Quarter1 sales for products Jeans or Shorts. You can use multiple SUMIFS or combine SUMIFS with SUM to get the same result. The formulae we applied in the above image are:

=SUMIFS(E5:E14,B5:B14,C16) + SUMIFS(E5:E14,B5:B14,C17)

Or,

=SUM(SUMIFS(E5:E14,B5:B14,C16), SUMIFS(E5:E14,B5:B14,C17))


How to Use SUMIFS to Sum Multiple Columns for Single Criterion in Excel

Sum of Quarter1 and Quarter2 sales for Jeans

The above image shows Jeans as the single criterion. We’ll sum both the quarter sales for this product. You can choose multiple SUMIFS or combine SUMIFS with SUM. The formulae we applied in the above image are as follows:

=SUMIFS(E5:E14,B5:B14,C16) + SUMIFS(F5:F14,B5:B14,C16)

Or,

=SUM(SUMIFS(E5:E14,B5:B14,C16), SUMIFS(F5:F14,B5:B14,C16))


How to Sum for Multiple Criteria in Different Columns and Rows in Excel

Using SUMPRODUCT for Quarter1 sales total for Shirt

However, if our criteria are in different columns and rows, we cannot apply SUMIFS anymore. The SUMPRODUCT can handle such cases. Here, our first criterion Shirt is in the Product column whereas, the second criterion Quarter1 is in the row. To get the desired sum we use the formula:

=SUMPRODUCT((E5:F14) * (B5:B14=J4) * (E4:F4=J5))

📕 Read More: Use SUMIFS for Multiple Criteria in Column and Row in Excel


How to Use Pivot Table to Sum for Multiple Criteria in Different Columns in Excel

Pivot Table output of total Quarter2 sales for White or Black Shirt

Some of us may not prefer to create formulas using functions if there was any other method available to perform tasks in Excel. Here too, we can apply Pivot Table to sum based on conditions in different columns. So, follow the process.

insert pivot table

First, you have to choose the dataset and click Insert > PivotTable as shown in the above image.

 dialog box asking to select a table or range for the pivot table and destination for placing the report

We already selected our dataset so you don’t have to worry about selecting a table/range. Choose New Worksheet and press OK.

Select desired fields and place them accordingly to get in the report output

Here, we place Product in the Rows, Color in Columns, and Quarter2 in the Values section.

Shirt from the Row Labels drop-down

As we want to see the sales figures of Shirt, we’ll only select Shirt from the Row Labels drop-down and press OK.

select Black and White from Column Labels drop-down

Next, we want the colors to be Black and White. So, we select Black and White from the Column Labels drop-down and press OK.

Pivot Table output of total Quarter2 sales for White or Black Shirt

At last, we’ll get to see the total Quarter2 sales of White and Black Shirt as sown in the above image.


Frequently Asked Questions

1. Can we add multiple ranges of data using SUMIFS in Excel?

Yes, we can add multiple ranges of data using SUMIFS. We just have to add multiple SUMIFS functions or combine SUMIFS with the SUM function.

2. What is the difference between SUMIF and SUMIFS?

SUMIF can only add data based on a single criterion, while SUMIFS can take on multiple criteria/range pairs.

3. How many arguments does the SUMIFS function have in Excel?

The SUMIFS function can take up to 127 pairs of Criteria Range & Criteria Arguments.


Conclusion

SUMIFS can alleviate many of our hassles, otherwise, we might have to add multiple SUM and IF functions to conditionally sum values. Again the SUMIF function can only take a single criterion. On the other hand, we don’t have to worry about criteria limitations in SUMIFS as it can take up to 127 criteria/range pairs. Therefore, the SUMIFS function makes our job a lot more convenient in Excel. Moreover, the criteria can be in different columns. We can easily perform logical operations as we have already shown in this article. We can also add multiple columns. The biggest advantage of this function is we don’t have to manually select values based on conditions. Just placing the criteria accordingly in the function argument will do its work for us. We hope you learned more about SUMIFS from this article and a little bit of practice can help you excel in your workplace.

For more content like this, please visit our site ExcelDen.com. Make sure to leave your feedback in the comments section.

(Visited 98 times, 1 visits today)
Shanto

Shanto

Hello! I am Shanto. Working as a Project Manager. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo