The **SUMIFS** function of **Exce**l 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.

**Example**

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

**=TODAY()-20**

- Also, use the following formula to assign today’s date.

**=TODAY()**

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

**=SUMIFS(E6:E11,D6:D11,”>=”&C14,D6:D11,”<=”&C15)**

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

**Example**

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

**=SUMIFS(E6:E11,C6:C11,C14,E6:E11,”<“&C15)**

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

**Example**

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

**=SUMIFS(E6:E11,C6:C11,C16,D6:D11,”>=”&C14,D6:D11,”<=”&C15)+SUMIFS(E6:E11,C6:C11,C17,D6:D11,”>=”&C14,D6:D11,”<=”&C15)**

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

**Example**

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

**=SUMIFS(E6:E11,B6:B11, “”,C6:C11, “”)**

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

**Example**

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

**SUMIFS with Non-Blank Cells**

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

**=SUMIFS(E6:E11, B6:B11, “<>”,C6:C11, “<>”)**

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

**SUMIFS with Non-Blank Cells**

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

**=SUM(E6:E11)**

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

**=SUMIFS(E6:E11,B6:B11,””,C6:C11,””)**

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.

**=SUM(E6:E11)-SUMIFS(E6:E11,B6:B11,””,C6:C11,””)**

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

**=SUM(SUMIFS(E6:E11,B6:B11,{“*Sapiens”,”*Cosmos”}))**

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