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.

**Table of Contents**hide

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

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")`

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")`

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")`

**Similar Readings:**

**SUMIFS Multiple Criteria Along Column and Row in Excel****How to Use SUMIFS formula with Multiple Criteria in Excel (11 Ways)**

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

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

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)`

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))`

And the final formula will be the same.

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

**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))))`

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)))`

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.