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.

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:

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.

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.

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:

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:

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:

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:

Or,

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

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:

Or,

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:

Or,

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:

Alternative to SUMIFS for OR Criteria in Different Columns:

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.

Or,

SUMIFS Alternative: SUMPRODUCT Function

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:

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

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:

Or,

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

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:

Or,

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

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:

📕 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

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.

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

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

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

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

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.

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.