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

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. |

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

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

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

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

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__

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

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

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

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

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

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

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

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

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.