SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)

SUMIFS Multiple Criteria AND Type in Excel

Today I will be showing how you can calculate the sum in any row or column matching multiple criteria using the SUMIFS() function in Excel.


Download Practice Workbook


Excel SUMIFS() Function

Before going to the main discussion, I would like to give a short introduction to Excel’s SUMIFS() function.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2]...)
  • Takes a range of cells called sum_range.
  • Takes one or more range of cells called criteria_range and one or more criteria alongside.
  • Returns the sum of the cells of the sum_range whose adjacent cells of criteria_range maintain the given criteria.

To know more about SUMIFS() function, visit this link.


SUMIF with Multiple Criteria in Column & Row in Excel

Let us have a look at this data set. We have a work record of a company named Armani Group.

We have the dates, items produced, personnel used and total sales in columns B, C, D, and E respectively.

 A data set in Excel

Now we will try to calculate a sum matching multiple criteria from this data set.


Case 1. AND Type SUMIF for Multiple Criteria

First of all, let’s try to match some multiple criteria of AND Type.

Here, AND Type criteria means you can take any datum only if all other data of the same row maintain the specified criteria.

For example, if anyone asks you any date when items produced were more than 200 and total sales were more than $10000.00, you can mention the date 3-Mar-21 from cell B15, but not the date 6-May-21 from cell B19.


Option 1: AND Type Of Different Columns

Now let’s try to calculate the total number of sales from January 01, 2021, when the number of Items produced was more than 100 and that of personnel used was more than 40.

Notice, there are 3 criteria in the problem.

  • First, the date must be greater than 1-Jan-2021.
  • Second, the number of items produced must be greater than 100.
  • And third, the number of personnel used must be greater than 40.

All 3 criteria are from 3 different columns.

The formula that we shall use is

=SUMIFS(E4:E20,B4:B20,">1-Jan-21",C4:C20,">100",D4:D20,">40")

SUMIF Multiple criteria AND type Different Columns

See, the total number of sales from January 01, 2021, when the number of Items produced was more than 100 and that of personnel used was more than 40 is $83000.

Read More: SUMIFS Multiple Criteria Different Columns (6 Effective Ways)


Option 2: AND Type of the Same Column

Now let us try a different case. Try to find out the total sales from 1-Jan-2020 to 31-Dec-2020.

Notice carefully, there are two criteria in this case.

  • The date must be greater than 1-Jan-2020.
  • And the date must be less than 31-Dec-2020.

But they are from the same column.

You can the formula of the same type used in case of different columns, like this

=SUMIFS(E4:E20,B4:B20,">1-Jan-2020",B4:B20,"<31-Dec-2020")

SUMIF Multiple criteria AND type Same Column

Now, can you tell me what the formula will be to calculate the total number of items produced when the number of personnel used was between 40 to 60?

Yes, you have guessed right. The formula will be

=SUMIFS(C4:C20,D4:D20,">40",D4:D20,"<60")

SUMIFS Multiple Criteria AND Type in Excel


Further Readings:


Case 2: OR Type SUMIF of Multiple Criteria


Option 1: OR Type of Different Columns

Now let us come to a different case.

If anyone wants to know the total sales in the days where items produced were more than 100 or personnel used were more than 40, then?

This problem falls under the category of multiple criteria of OR Type.

This is a bit tricky. But no worries. We will solve this pretty sophistically.

First of all, take a new column and insert this formula in the first cell of the column

=(--(C4>100))+(--(D4>40))

I enter this in cell E4.

Boolean Formula in Excel

Now drag this formula to the rest of the cells through the Fill Handle.

Dragging Fill Handle in Excel

I name this column as Condition Fulfilled.

Now go to the cell where you want the total sales and enter this formula

=SUMIF(F4:F20,">0",E4:E20)

OR Type SUMIF MUltiple Criteria in Excel

See we have got the total sales when Items Produced were more than 100 or the Personnel Used ore more than 40.

This is $128000.00


Option 1: OR Type of the Same Column

You can apply this procedure for three or more criteria, or multiple criteria of the same column.

For example, to calculate the total sales when the personnel used were less than 40 or greater than 50, the formula in the Condition Fulfilled column will be

=(--(D4<40))+(--(D4>50))

Boolean Formula maintaining criteria of the same column

And the final formula will be the same.

=SUMIF(F4:F20,">0",E4:E20)

OR Type SUMIF MUltiple Criteria Same column in Excel


Alternative Options

There are some alternative options through which you can achieve the same goal.

And in fact, they are a bit easier sometimes.


Option 1: Using SUMPRODUCT() Function


i. AND Type SUMPRODUCT() of Multiple Criteria

To calculate the total number of sales from January 01, 2021, when the number of Items produced was more than 100 and that of personnel used was more than 40, the formula will be

=SUMPRODUCT(E4:E20,(--(((B4:B20)>DATE(2021,1,1))*((C4:C20)>100)*((D4:D20)>40))))

AND Type SUMPRODUCT Multiple Criteria

See, we have got the same result as before, when using SUMIFS() function, $83000.


ii. OR Type SUMPRODUCT() of Multiple Criteria

To calculate the total number of sales when items produced were more than 100 or Personnel used were more than 40, the formula will be

=SUMPRODUCT(E4:E20,(--((--((C4:C20)>100))+(--((D4:D20)>40))>0)))

OR Type SUMPRODUCT Multiple Criteria

See, we have got the same result as earlier, $128000.


Conclusion

So, using these methods we can calculate any sum maintaining multiple criteria in Excel. Do you have any questions? Feel free to ask us in the comment section.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo