Lamisa Musharrat
The SUMIFS function of Excel can be used to add all the data that meet single or multiple criteria. This can be useful while handling huge datasets and can make the tiresome process a bit easier for you. Here, you will find how to use SUMIFS with multiple criteria along column and row in Excel.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to Use SUMIFS with Multiple Criteria Along Column and Row in Excel with These 5 Suitable Approaches
We are going to discuss five unique ways to use SUMIFS with Multiple Criteria Along Columns and Rows in Excel. Here, the dataset we used is an online bookshop sales record. This dataset consists of columns of names of the books sold, area to deliver, date of delivery, and sales. In different methods, various criteria are used.
1. Using SUMIFS in the Column with Date Criteria
Suppose we want to know the total sales of books within 20 days before today. We can do this only by using the SUMIFS function. Here are the steps you will follow to use SUMIFS with multiple criteria in column and row .
⬇️⬇️ STEPS ⬇️⬇️
- Specify the dates using functions or manually. Here I have used the TODAY function to grab today’s date and the date of 20 days ago.
- Use the following formula to get the date of 20 days ago.
- Also, use the following formula to assign today’s date.
- Then, specify a cell where you want to count the total sales of the bookstore.
Here, I have taken cell D14.
- After that, type the formula on that cell. You can see from the screenshot that I have entered the formula in my cell D14.
- Here is the formula for you.
🔨 Formula Breakdown
SUMIFS(E6:E11,D6:D11,”>=”&C14,D6:D11,”<=”&C15)
👉 Firstly, select the range E6:E11 in the 1st argument, for the value we want.
👉 Then, select the range D6:D11 in the 2nd argument, which contains the dates cells, and enter the sign ( “>=”&)followed by the first criteria, which is assigned in the cell C14 Which means dates greater than or equal to cell C14 will be considered to count.
👉 Similarly, add the 2nd criteria that are less than equal ( “<=”&) in the same range cell D6:D11 and select cell C15 as the ending date.
- After that, press Enter from your keyboard and see the result in your specific cell.
2. Applying SUMIFS with Comparison Operators
Comparison operators are very common, and you can use these to build your function combined with the SUMIFS function. Now, if multiple criteria are from two different cells, you can follow the below procedures.
⬇️⬇️ STEPS ⬇️⬇️
- First, assign your criteria to two different cells. You can see I have selected the cells C14 and C15 to assign my criteria which are the region Alaska and Sales of $300. Notice that these two criteria are from two different columns.
- Now it’s time to write a formula, but don’t forget to select the cell where you will write the formula. I have selected cell D14.
- Then, write the formula starting with the SUMIFS function, don’t forget to put the equal “=” sign first.
- Here is the entire formula that you are going to use.
🔨 Formula Breakdown
SUMIFS(E6:E11,C6:C11,C14,E6:E11,”<“&C15)
👉 Select the cell range E6:E11 in the 1st argument, which value we need.
👉 Then, select the range C6:C11 in the second argument and take cell C14 as the first criterion for Alaska, meaning cells containing only Alaska will be considered to sum. Similarly, add the 2nd criterion of range E6:E11 that denotes the sales.
👉 After that, take smaller than sign (<) and cell D15, which is the second criteria that is $300.This means only sales less than $300 will be considered to sum.
- Then, press Enter from your keyboard and see the result in your specific cell.
3. Applying Multiple SUMIFS for Multiple OR Criteria
Now we are going to use the SUMIFS function multiple times for multiple criteria. We are going to estimate the total sales of two areas within a specific period of time. This function is a little lengthy and complex, but don’t be afraid to try this out, as I have simplified these steps for you below.
⬇️⬇️ STEPS ⬇️⬇️
- First, write your criteria in specific cells; I have entered two dates in cells C14 and C15 as my criteria for a specific time and two areas in cells C16 and C17 where I want to calculate the total sales.
- Then, select another cell where the calculation will take place; in my dataset, this is cell D14.
- Finally, enter the following formula in that specific cell.
- Here is the full formula for you.
🔨 Formula Breakdown
SUMIFS(E6:E11,C6:C11,C16,D6:D11,”>=”&C14,D6:D11,”<=”&C15)+SUMIFS(E6:E11,C6:C11,C17,D6:D11,”>=”&C14,D6:D11,”<=”&C15)
The first part of the formula SUMIFS(E6:E11,C6:C11,C16,D6:D11,”>=”&C14,D6:D11,”<=”&C15)
👉 Here, the selected range E6:E11 in the first argument is the range for sales for the value we want.
👉 After that, the range C6:C11 is the second argument, and the cell C16 is the 1st criterion for Texas. Then the range of Shipping dates is selected to look for the other criteria, which is cell C14, the Starting Date. The “>=” sign before this indicates to look for less than or equal to the criteria.
👉 Then, cell range D6:D11 is the range of shipping date where to look for the other criteria assigned in C15, and this command is given by the operator “<=” which indicates that the sum will be found for the dates less than or equal to the value of cell C15.
The second part of the formula
SUMIFS(E6:E11,C6:C11,C17,D6:D11,”>=”&C14,D6:D11,”<=”&C15)
👉 It is the same as the first part of the formula; the only exception here is that the area criteria have now been changed, and this part works with another criterion which is stored in cell C17. That means it will rerun this function just for the new criteria in Alaska.
- Finally, the addition sign in the middle is to add the two parts of the formula and gives the final value.
- Then, press Enter from your keyboard and see the result in your specific cell.
4. Utilizing SUMIFS with Blank Rows Criteria
You can also use this SUMIFS function with blank rows. Here you can see in my data set, there are some blank cells.
Follow the below steps to see how to do that.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where the calculation will take place; in my data set, this is cell D14.
- Then, write the formula in that cell using SUMIFS.
- Here is the formula that I have used.
🔨 Formula Breakdown
SUMIFS(E6:E11,B6:B11, “”,C6:C11, “”)
👉 The cell range E6:E11 is the range of Sales where I want to do the sum.
👉 Cells B6:B11 is the Book Name range.
👉 “ “ checks for the blank cells in the range assigned in the previous argument, which is B6:B11.
👉 The range of cell C6:C11 is the range of areas.
👉 Again,” ” checks for the blank cells in the range of C6:C11.
- Then, press Enter from your keyboard and see the result in your specific cell.
5. Use of SUMIFS with Non-Blank Cells Criteria
In the previous approach, how to use SUMIFS with various criteria column and row to find sum of the data which have blank cells in the dataset. However, Let’s see how to utilize this to calculate the non-blank cells in a dataset where blank rows are present. There are two ways to do this.
5.1. Using SUMIFS Function
You can use SUMIFS function only. Here are the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where the calculation will take place; in my dataset, this is cell D14.
- Then type the following formula in that cell.
🔨 Formula Breakdown
SUMIFS(E6:E11, B6:B11, “<>”,C6:C11, “<>”)
👉 The cell range E6:E11 is the range of Sales where I want to do the SUM.
👉 Cells B6:B11 is the Book name range.
👉 “<>” checks for the blank cells in the range assigned in the last argument, which is B6:B11.
👉 C6:C11 is for the range of Areas.
👉 Again,“<>” checks for the blank cells in the range of C6:C11.
- As a result, it will show an output that is the sum If both the column’s same cell is non-blank.
- Then, press Enter from your keyboard and see the sum of the non-blank cells in your specific cell.
5.2. Applying the Combination of SUM and SUMIFS
Also, you can use the combination of SUM and SUMIFS functions. Just follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, you must calculate the total sales number in all cells. To do that, enter the following formula in a specific cell.
- Here, E6:E11 is the range of sales where I want to do the SUM of all sales.
- After that, to count the sales of the blank cells, enter the following formula in a specific cell.
Here,
E6:E11 is the range of sales where I want to do the SUM.
C6:C11 is the range of areas.
B6:B11 is the book name range.
- Then, click on Enter from your keyboard.
- After that, you have to subtract these two, and then you can get the sales that are non-blank. Type the following formula in the cell where you want to see this result.
🔨 Formula Breakdown
SUM(E6:E11)-SUMIFS(E6:E11,B6:B11,””,C6:C11,””)
The first part of the formula SUM(E6:E11)
👉 Give the sum of the entire range of Sales, cells E6:E11.
Second part of the formula SUMIFS(E6:E11,B6:B11,””,C6:C11,””)
👉 The cell range E6:E11 is the range of Sales where I want to do the SUM.
👉 Cells B6:B11 indicates the range of Book Name.
👉 ” “checks for the blank cells in the range assigned in the previous argument, B6:B11.
👉 C6:C11 belongs to the Area Column.
👉 Again,” ” checks for the blank cells in the range of C6:C11.
And the subtraction sign is to subtract the value of blank cells from all cell’s values and get the value of blank cells only.
- Then, press Enter from your keyboard and see sum of the non-blank cells in your specific cell.
How to Use SUMIFS with Multiple Criteria in the Same Column
Suppose you want to use SUMIFS with multiple criteria column and row to evaluate a sum, and these criteria are in the same column.
Therefore, you can also use the SUMIFS function, and we are going to show you one of the ways below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a specific cell where you will see the result, and here I have used the cell D15.
- Then, enter the formula written below.
🔨 Formula Breakdown
SUM(SUMIFS(E6:E11,B6:B11,{“*Sapiens”,”*Cosmos”}))
👉 The inner function is SUMIFS. Using this function, we find the rows where the book name is Sapiens or Cosmos. If the rows are matched, it will sum up the sales and show the result.
👉 E6:E11 is the range of sales where I want to do the SUM.
👉 B6:B11 indicates the book name range.
👉 And, Sapiens and Cosmos are the criteria that I want to calculate the sales.
- Then, press Enter from your keyboard and see the sum of the non-blank cells in your specific cell.
📝 Takeaway from This Article
📌 You have learned how to utilize the SUMIFS function for several criteria in multiple columns and rows.
📌 Summation can be done using the SUMIFS function for both Non-blank and blank cells.
📌 We have also illustrated how to use this SUMIFS if the multiple criteria belong to the same column.
Conclusion
As you can see from this article SUMIFS function can save you time while dealing with ample amounts of data. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
