SUMIF with Multiple Criteria for Different Columns in Excel

This article is all about how to SUMIF with multiple criteria for different columns in Excel.

While working with Excel, sometimes we need to perform SUM operations by adding criteria. That time we need to use the SUMIF function.

Here, we used a data set of T-shirts selling of a shop.

Data set to apply SUMIF with multiple criteria for different columns


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to SUMIF function

The SUMIF function sum the values in a range that meet the criteria that we specify.

Syntax

SUMIF (range, criteria, [sum_range])

Argument

range – The range of cells that we want to evaluate by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

criteria – The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

Note: Any text criteria or any criteria that include logical or mathematical symbols must be enclosed in double quotation marks (“). No quotation mark is needed if the criteria are numeric.

sum_range – The actual cells to add if we want to add cells other than those specified in the range argument. sum_range should be the same size and shape as the range.


4 Methods to Use SUMIF with Multiple Criteria for Different Columns in Excel

We will add Criteria and Outcome cells to our data set. We may apply multiple criteria and then we will get our outcomes.


1. Excel SUMIF with Multiple Criteria

Here, we will apply multiple criteria only using the SUMIF function. We can apply as many criteria as we need.

In this section, we want to see the total price of Mike and David together.

Step 1:

  • Go to cell D15.
  • Write the SUMIF function two times and complete the formula. So, the formula becomes:
=SUMIF(C5:C11,D13,E5:E11) + SUMIF(C5:C11,D14,E5:E11)

SUMIF with Multiple Criteria

Step 2:

  • Then press Enter.

Formula Breakdown

  • SUMIF(C5:C11,D13,E5:E11)

It will search D13 in the range C5:C15 and show output from range E5:E15.

  • SUMIF(C5:C11,D14,E5:E11)

It will search D14 in the range C5:C15 and show output from range E5:E15.

Read More: SUMIF Multiple Ranges [6 Useful Ways]


2. Combination of SUM and SUMIF with Multiple Criteria

In this section, we will use the SUM and SUMIF functions to perform an operation.

We will find the total price of Mike, David together.

Step 1:

  • Go to cell D15.
  • First, we will write the SUMIF function and then use the SUM function with this SUMIF So, the formula becomes:
=SUM(SUMIF(C5:C11,D13:D14,E5:E11))

Combination of SUM and SUMIF with Multiple Criteria

Step 2:

  • Then press Enter.

Formula Breakdown

  • SUMIF(C5:C11,D13:D14,E5:E11)

It will search D13, D14 in the range C5:C15 and show output from range E5:E15. It will provide two outputs as we compare two values.

  • SUM(SUMIF(C5:C11,D13:D14,E5:E11))

It will return the sum of the outputs of the SUMIF function.

Read More: Sum Multiple Columns Based on Multiple Criteria in Excel


3. Use of SUMIF & SUMPRODUCT Functions with Multiple Criteria

We will use the SUMPRODUCT function in this method to get our outcome.

Like the previous example, we will use Mike and David to get the total price of the T-shirt.

Step 1:

  • First, enter the D15 cell.
  • Write the SUMIF function first and add the SUMPRODUCT function with this. So, the formula becomes:
=SUMPRODUCT(SUMIF(C5:C11,D13:D14,E5:E11))

SUMIF & SUMPRODUCT Functions with Multiple Criteria

Step 2:

  • Then press Enter.

Formula Breakdown

  • SUMIF(C5:C11,D13:D14,E5:E11)

It will search cells D13, D14 in the range C5:C15 and show output from range E5:E15. It will provide two outputs here.

  • SUMPRODUCT(SUMIF(C5:C11,D13:D14,E5:E11))

It will return the sum of the outputs of the SUMIF function.


4. Excel SUMIF with Multiple Criteria From Different Columns

In this section, we will apply the SUMIF function to perform multiple criteria from different columns. We will use only the SUMIF function here.

Our criteria are, we will find the products of Mike and the Product ID A-007 and get their price together.

Step 1:

  • Go to cell D13 first and write Mike.
  • Then go to D14 and write A-007. Set ore both criteria in the data set.

SUMIF with Multiple Criteria From Different Columns

Step 2:

  • Now, go to cell D15.
  • Write two SUMIF.  1st one is for Mike and the 2nd one is for A-007. So, the formula becomes:
=SUMIF(C5:C11,D13,E5:E11) + SUMIF(B5:B11,D14,E5:E11)

SUMIF with Multiple Criteria From Different Columns

Step 3:

  • Then press Enter.

Formula Breakdown

  • SUMIF(C5:C11,D13,E5:E11)

It will search cells D13 in the range C5:C15 and show output from range E5:E15.

  • SUMIF(B5:B11,D14,E5:E11)

It will search cells D14 in the range B5:B15 and show output from range E5:E15.

  • SUMIF(C5:C11,D13,E5:E11) + SUMIF(B5:B11,D14,E5:E11)

It will return an OR operation output.

Read More: Use of the SUMIF Function across Multiple Columns in Excel (4 Methods)


SUMIFS with Multiple Criteria for Different Columns in Excel

In this segment, we will use the SUMIFS function to satisfy multiple criteria in different columns.

We will show AND and OR operations with SUMIFS here.

1. Excel SUMIFS with Multiple AND Criteria

We want to get the total price of John bought and those products price is higher than 22 dollars.

Step 1:

  • Set John in Cell D13 and 22 on Cell D14.

Excel SUMIFS with Multiple AND Criteria

Step 2:

  • Go to cell D15.
  • Write the SUMIFS In the 1st argument select range E5:E11 and it will return after completing conditions. So, the formula becomes:
=SUMIFS(E5:E11,C5:C11,D13,E5:E11,">"&D14)

Step 3:

  • Then press Enter.

Excel SUMIFS with Multiple AND Criteria

Formula Breakdown

  • SUMIFS(E5:E11,C5:C11,D13,E5:E11,”>”&D14)

It will search cells D13 in the range C5:C15 and greater than D14 from the range E5:E15. And give output from range E5:E11.

It will return an AND operation output.


2. Excel SUMIFS with Multiple OR Criteria

We will perform OR operations here. Three criteria are set here.

Step 1:

  • Set John, Mike, and 22 in cells D13, D14, D15 respectively.

SUMIFS with Multiple OR Criteria

Step 2:

  • Go to cell D15.
  • Write the SUMIFS two times function and complete the formula. So, the formula becomes:
=SUMIFS(E5:E11,C5:C11,D13,E5:E11,">"&D15)+SUMIFS(E5:E11,C5:C11,D14,E5:E11,">"&D15)

Step 3:

  • Then press Enter.

SUMIFS with Multiple OR Criteria

Formula Breakdown

  • SUMIFS(E5:E11,C5:C11,D13,E5:E11,”>”&D15)

It will search cell D13 in the range C5:C15 and see which is greater than D15 in the range E5:E11. If both conditions fulfill then get out from the 1st argument range E5:E11.

  • SUMIFS(E5:E11,C5:C11,D14,E5:E11,”>”&D15)

It will search cell D14 in the range C5:C15 and see which is greater than D15 in the range E5:E11. If both conditions fulfill then get out from the 1st argument range E5:E11.

  • SUMIFS(E5:E11,C5:C11,D13,E5:E11,”>”&D15)+SUMIFS(E5:E11,C5:C11,D14,E5:E11,”>”&D15)

It will return an OR operation output of the respective functions.


Conclusion

Here, we explained some methods to SUMIF multiple criteria different columns. I hope, this will help you to get your solution. We also used other function methods so that you can compare. If you have any suggestions and modifications, please comment on the box.


Related Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo