How to Use SUMIFS Function in Excel (6 Handy Examples)

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.

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:

• Return Parameter:

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

How to Use SUMIFS Function in Excel: 6 Handy Examples

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:

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

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:

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

Read more: How to Use SUMIFS to SUM Values in Date Range in Excel

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:

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

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:

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

Read more: Excel SUMIFS Multiple Criteria

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:

Or,

➤ 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:

➤ 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:

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

Excel SUMIFS Function Example Download

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

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.

<< Go Back to Excel Functions | Learn Excel