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

Here’s an overview of applying the SUMIFS function in Excel.

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 that meet all the given criteria.

How to Use the SUMIFS Function in Excel – 6 Handy Examples

Example 1 – SUMIFS with a Single Condition in Excel

We have the sales record of computers throughout the month. We’ll sum the total sales for a single criterion, such as for devices from the Inchip brand.

Steps:

• Cell C26 contains the brand name we’re searching by.
• In the output Cell B29, insert the following:

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

Example 2 – Using SUMIFS with Date Conditions in Excel

We want to know the total sales for all notebooks that were released after 30 April 2021.

Steps:

• D26 contains the value Notebook which is one condition.
• Select the output Cell C30 and insert:

The date condition is hard-coded into the formula. However, you can enter it as text in a cell such as D27, it just needs to be in the exact format as in the formula.

• 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

Example 3 – Using the SUMIFS Function while Excluding Blank Cells in Excel

We can find the total sales value of notebook devices that have complete information in the table.

Steps:

• In the output Cell B30, apply the following formula:

• Hit Enter.

Example 4 – SUMIFS with Multiple OR Logic in Excel

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:

• The conditions are in D26, D27, F26, and F27 (Notebook, USA, Desktop, Japan).
• In Cell B30, insert the following:

• Press Enter and you’ll get the desired result.

Read more: Excel SUMIFS Multiple Criteria

Method 5 – Inserting Wildcard Characters inside SUMIFS Function in Excel

We want to know the total sales of desktop models beginning with ‘OC’.

Steps:

• In the output Cell C30, use the following formula:

Or,

• Press Enter.

Example 6 – Combining SUM and SUMIFS Functions in Excel

We want to find the total sales of all notebook devices that came from the USA and Japan.

Steps:

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

• Press Enter.

For an array input inside the SUMIFS function, the function will also return the evaluated sums in an array. The SUMIFS function will return with the total sales of the notebooks from the USA and Japan separately. The SUM function will then add the elements of the array.

SUMPRODUCT as an Alternative to the SUMIFS Function

We want to determine the total sales of the notebooks from the USA and Japan.

Steps:

• Insert the following formula in the output cell and press Enter.

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. The SUMPRODUCT function will let you find the total sum from an array formula.

Things to Keep in Mind

The SUMIFS function will return the #SPILL error if you input an array condition inside and the function finds a merged cell in the 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, 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 an error.

Excel SUMIFS Function Example Download

<< Go Back to Excel Functions | Learn Excel