SUMIFS Multiple Criteria Different Columns (6 Effective Ways)

Enter the Vendor name and quantity then press Enter

Sometimes we may need to get a summation based on different criteria. For this purpose, Excel provides a function called SUMIFS. This is the updated version of the SUMIF function. In this article, I will show how to use SUMIFS for multiple criteria in different columns.

Download the Practice WorkBook

What is the SUMIFS Function?

The syntax of this function is like this:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Let’s see the details of every argument.

Sum_range -> This portion contains the range of cells to sum.

Criteria_range1 -> Here we need to pass the range that is tested using the first criteria.

Criteria1 -> This is our first criteria or condition.

[criteria_range2, criteria2], … -> This means we can pass the criteria range and criteria as much as we want.

6 Ways to Use SUMIFS With Multiple Criteria in Different Columns


1. SUMIFS with Comparison Operators

As I have mentioned the SUMIFS function uses logical conditions that’s why we can use it for different comparison purposes. For this, SUMIFS allows using a comparison operator (<, =, >) to do a comparison. Let’s assume we have a dataset of office setup. In our dataset Product Name, Vendor, Quantity, and Price. Our task is to find the total quantity by specifying the vendor name and a quantity ( the selected quantity should be equal to or above this quantity)

SUMIFS with Comparison Operators

Step 1: Enter the formula in cell H5

=SUMIFS(D4:D14,C4:C14,H3,D4:D14,H4)

Formula Explanation

  • D4:D14 This is the Quantity range that will be summed up.
  • C4:C14 this range contains the Vendor’s list which will be matched with input which is in the H4 cell.
  • D4:D14 this range contains the Quantity range which will be matched with input field H5.

Step 2: Enter the Vendor name and quantity then press Enter

Enter the Vendor name and quantity then press Enter

[ Note: As we want to total quantity which is equal or greater than input quantity that’s why we need to put >= sign before the quantity number.]


2. SUMIFS with Dates

Here we will see how to deal with the SUMIFS function with dates. For this, we will consider the same dataset above with an extra column named Delivery Date. Now, our task is to find the total quantity which was delivered by any specific vendor in the last 20 days from today. I like to mention that for me today is 19th July 2021. Our all calculations will be calculated based on this date.

 SUMIFS with Dates

Step 1: Enter the formula in cell I4

=SUMIFS(D4:D14, F4:F14,">="&TODAY()-20, F4:F14,"<="&TODAY(),C4:C14,I3)

Formula Explanation

  • D4:D14 this is the range where we will calculate the total summation.
  • F4:F14 this is the criteria range and “>=”&TODAY()-20 this is the criteria for the first range. As we want to calculate the last 20 dates that’s why we are using the TODAY function and TODAY()-20 gives the last 20 dates from today.
  • F4:F14,”<=”&TODAY() this the highest range of dates. It will calculate the dates up to today.
  • C4:C14, I3 Here we are matching the vendor name with our vendor list.

Enter formual with SUMIFS and Today function

Step 2: Enter any vendor name and press Enter

 Enter any venfor name and press enter


3. SUMIFS with Blank Cells

Now let’s see how to count blank cells using the SUMIFS function. For this method, we just need to modify the previous dataset. Here we will find out the total quantity for any specific vendor which has not been delivered yet. Now how can we measure that the product is delivered or not? Well, we just simply do not put any data in data in the Delivery date column.

SUMIFS with Blank Cells

Step 1: Enter the formula in cell I4

=SUMIFS(D4:D14, F4:F14,"=",C4:C14,I3)

Formula Explanation

  • D4:D14 this is the sum range.
  • F4:F14,”=” this portion checks the blank ranges in the delivery date column.
  • C4:C14, I3 it finds the matched rows with our input.

Enter the formula in I4

Step 2: Enter any vendor name and press Enter

Enter any vendor name and press enter


4. SUMIFS with Multiple OR Criteria

Here we will see how we can use the SUMIFS function where it will behave like OR logic. OR logic means if any of the conditions are met then it will be counted as true. Again, our dataset will be the same as above. Our target is to find the supplied product quantity for any specific two vendors in a specific month.

SUMIFS with multiple OR criteria

Step 1: Enter the formula in the I6 cell

=SUMIFS(D4:D14,C4:C14, I4, F4:F14,">=7/1/2021", F4:F14, "<=7/31/2021") + SUMIFS(D4:D14, C4:C14, I5, F4:F14, ">=7/1/2021", F4:F14, "<=7/31/2021")

Formula Explanation

Here we have used two SUMIFS functions. Two functions work the same way for different vendors. Let’s see the details of one of them.

  • SUMIFS(D4:D14,C4:C14, I4, F4:F14,”>=7/1/2021″, F4:F14, “<=7/31/2021”) this is for vendor 1.
  • D4:D14 is the range where we will calculate the sum.
  • C4:C14, I4 is for matching the 1st vendor name from the vendor column.
  • F4:F14,”>=7/1/2021″ and F4:F14, “<=7/31/2021” these two portions define the range of July month.

 Enter the formula in I6

Step 2: Enter any two vendors’ names and press Enter

Enter any two vendors’ names and press Enter


5. Using Array Argument in SUM & SUMIFS

We can simplify the formula which was used in the previous method (method 4) by using an array argument where the functions will be SUM and SUMIFS

Step 1: Enter the formula in I6

=SUM(SUMIFS(D4:D14,C4:C14, {"David","Wayne"}, F4:F14,">=7/1/2021", F4:F14, "<=7/31/2021"))

Formula Explanation

  • Inside the SUMIFS function here we have used an array argument as criteria which is {“David”, “Wayne”} this will find the elements which are in the array from C4 to C14 range.
  • Lastly, the SUM function will calculate the total selected quantities and show them.

Enter formula with array argument in SUM and SUMIFS


6. Alternative of SUMIFS Function

Using SUMPRODUCT Function

We can do the same operation of SUMIFS with another Excel function which is SUMPRODUCT. Again, for this, we will use the same dataset that we were using. Our target is to find the total price equal to or above any specific quantity for two vendors.

Using Sumproduct function

Step 1: Enter the formula in cell I6

=SUMPRODUCT(--(D4:D14>=I3), --(ISNUMBER(MATCH(C4:C14, I4:I5,0))), E4:E14)

Formula Explanation

  • –(D4:D14>=I3) this part finds the quantities greater or equal to the input.
  • –(ISNUMBER(MATCH(C4:C14, I4:I5,0))) this part matched the vendor’s name.
  • E4:E14 this is the range that will be summed up.

Enter the formula with sumproduct

Step 2: Enter the quantity and two vendors names and check the output

Enter the quantity and two vendors names and check the output


Things to Remember About SUMIFS Function

  • In Excel, the order of arguments of the SUMIF and SUMIFS functions are different. In particular, sum_range is the first (1st) parameter in SUMIFS, but it is the third (3rd) in SUMIF.
  • In the SUMIFS function the sum range and criteria, the range should be equally filled.

Conclusion

These are the ways to use SUMIFS for multiple criteria with multiple columns. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo