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.
=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
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.
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
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 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
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
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
And the final formula will be the same.
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
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
See, we have got the same result as earlier, $128000.
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.