In Microsoft Excel, the SUMIFS function is used to evaluate the sum from a range of cells under multiple conditions. In this article, you’ll learn how you can use this SUMIFS function efficiently in Excel with proper illustrations.

The above screenshot is an overview of the article, representing an application of the SUMIFS function in Excel. You’ll learn more about the dataset as well as the methods to use the SUMIFS function properly in the following sections of this article.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Introduction to the SUMIFS Function**

**Function Objective:**

*Add the cells given by specified conditions or criteria.*

**Syntax:**

*=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)*

**Arguments Explanation:**

Arguments | Required/Optional | Explanation |
---|---|---|

sum_range |
Required |
Range of cells that has to be summed under conditions or criteria. |

criteria_range1 |
Required |
Range of cells where the criteria or condition will be applied. |

criteria1 |
Required |
Condition for the criteria_range1. |

[criteria_range2] |
Optional |
2nd range of cells where the criteria or condition will be applied. |

[criteria2] |
Optional |
Condition or criteria for the criteria_range2 |

**Return Parameter:**

*The sum of the cells in a numeric value meeting all given criteria.*

**6 Handy Examples of Using SUMIFS Function in Excel**

**1. SUMIFS with a Single Criterion in Excel**

Let’s get introduced to our dataset first that we’ll use for all possible uses of **SUMIFS** function under different criteria. The picture below represents the sales of some random computer devices in a month. In this section, we’ll use the **SUMIFS** function to sum the total sales for a single criterion. We’ll evaluate the total sales for all devices of the Inchip brand here.

**📌**** Steps:**

➤ In the output **Cell B29**, we have to type:

`=SUMIFS(G5:G23,B5:B23,C26)`

➤ Press **Enter** and you’ll get the total sales for Inchip devices from the table.

**Read More:** **How to Use SUBTOTAL Function in Excel (3 Suitable Examples)**

**2. Use of SUMIFS with Dates Criteria in Excel**

We can insert dates inside the **SUMIFS** function with comparison operators and thereby evaluate the sum under multiple criteria. Assuming that we want to know the total sales for all notebooks that were released after **30 April 2021**.

**📌**** Steps:**

➤ Select the output **Cell C30** and type:

`=SUMIFS(G5:G23,C5:C23,D26,F5:F23,">4/30/2021")`

➤ Press **Enter** and the function will return the total sales for all notebook devices that were released after 30 April 2021.

**Read More:** **44 Mathematical Functions in Excel (Download Free PDF)**

**3. Using SUMIFS Function with Excluding Blank Cells in Excel**

Sometimes our dataset or table may have some blank cells. If we want to exclude those rows containing blank cells then we have to use the** ‘Not Equal to’(<>)** operator as the criteria for a range. Now based on our dataset, we can find the total sales value of notebook devices that are lying with sufficient and complete data in the table.

**📌**** Steps:**

➤ In the output **Cell B30**, the related formula will be:

`=SUMIFS(G5:G23,C5:C23,D26,D5:D23,"<>",E5:E23,"<>")`

➤ After pressing **Enter**, the resultant value will be shown at once.

**Similar Readings**

**How to Use SEQUENCE Function in Excel (16 Examples)****SUMPRODUCT() function in Excel****How to use RAND function in Excel (5 Examples)****Solving equations in Excel (polynomial, cubic, quadratic, & linear)****How to Use MOD Function in Excel (9 Suitable Examples)**

**4. SUMIFS with Multiple OR Logic in Excel**

We may need to extract the sum for multiple criteria that are impossible with only one use of the **SUMIFS** function. In that case, we can simply add two or more **SUMIFS** functions for multiple criteria. For example, we want to evaluate the sum of total sales for all notebooks that originated in the USA and all desktops that originated in Japan.

**📌**** Steps:**

➤ In **Cell B30**, the related formula with two **SUMIFS** functions will be:

`=SUMIFS(G5:G23,C5:C23,D26,E5:E23,D27)+SUMIFS(G5:G23,C5:C23,F26,E5:E23,F27)`

➤ Now press **Enter** and you’ll get the desired result right away.

**Read More:** **51 Mostly Used Math and Trig Functions in Excel**

**5. Inserting Wildcard Characters inside SUMIFS Function in Excel**

Use of the wildcard characters(*, ?, ~) will let you find the exact text value that you may not be able to remember for a while. For example, we want to know the total sales of some desktop model names beginning with **‘OC’**.

**📌**** Steps:**

➤ In the output **Cell C30**, the related formula should be:

`=SUMIFS(G5:G23,C5:C23,D26,D5:D23,D27)`

Or,

`=SUMIFS(G5:G23,C5:C23,D26,D5:D23,"*OC*")`

➤ Press **Enter **and the formula will return the total sales for the defined criteria.

**6. Combining SUM and SUMIFS Functions in Excel**

If you need to evaluate the sum for multiple criteria in a single column then you have to make an array formula that has to be encircled with **SUM or SUMPRODUCT** functions. Assuming, we want to find out the total sales of all notebook devices that were originated in the USA and Japan.

**📌**** Steps:**

➤ The combined formula with **SUM and SUMIFS** functions in **Cell C31** will be:

`=SUM(SUMIFS(G5:G23,C5:C23,D26,E5:E23,{"USA","Japan"}))`

➤ Now press **Enter** and you’ll get the total sales value under the defined criteria.

Although the **SUMIFS** function is used to evaluate the sum under multiple criteria, for an array input inside the **SUMIFS** function, it’ll also return the evaluated sums in an array. That means, based on our criteria in this section, unless we don’t use the **SUM** function outside the **SUMIFS** function, then only the **SUMIFS** function will return with the total sales of the notebooks originated by the USA and Japan separately. Using the **SUM** **function** outside, we’ll evaluate the total sum for those two separate and extracted data.

**An Alternative to SUMIFS Function**

A suitable alternative to the **SUMIFS** function is the **SUMPRODUCT** **function**. From the table below, if we want to determine the total sales of the notebooks originated in the USA and Japan, let’s find out what the formula could look like in the following steps.

**📌**** Steps:**

➤ We have to type in the output **Cell B31**:

`=SUMPRODUCT((G5:G23)*(C5:C23=D26)*(E5:E23={"USA","Japan"}))`

➤ After pressing **Enter,** you’ll get a similar result found in the previous section of the article.

The basic difference between the uses of **SUMIFS** and **SUMPRODUCT** functions is- in the **SUMIFS** function you have to add and separate the range of cells and criteria with **Commas (,) **whereas you have to use **Asterisk (*) **symbol to input multiple criteria inside the **SUMPRODUCT** function. Another major difference is that the **SUMIFS** function is not able to produce a complete sum with an array formula but the** SUMPRODUCT **function will let you find the total sum from an array formula with much ease.

**💡**** Things to Keep in Mind**

🔺 **SUMIFS** function will return **#SPILL** error if you input an array condition inside and at the same time the function finds a merged cell as the return destination.

🔺 If you input an array condition inside the **SUMIFS** function, it’ll return the sums for those defined conditions in an array.

🔺 If you need to evaluate the sum with a single criterion, then you can use the **SUMIF** **function** instead of **SUMIFS**.

🔺 Unless you use the **Double-Quotes(“ “)** outside a text value as range criteria, the function will return **zero(0)** instead of showing any error. So, you have to be careful while inputting text value as the criteria inside the SUMIFS function.

**Concluding Words**

I hope all of the methods mentioned above to use the** SUMIFS** function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.