Excel **SUMIFS** function is a statistical function that returns the sum of the cell values in a range that meets one specified criterion (single criteria) or more specified criteria (multiple criteria).

Whereas Excel **SUMIF **function is a statistical function that returns the sum of cell values in a range that meets only one specified criterion (single criteria).

# I. Features of Excel SUMIFS Function:

(01) The Excel SUMIFS function is generally used to get the summation of range values based on a match of a single criterion or multiple criteria.

(02) Criteria can be a number, logical expression, cell reference, text, date, blanks or another Excel function. For example, 50, “➪=50”, A1, “Jackson”, “10/10/2019”, “”, or TODAY().

(03) The first argument of the Excel SUMIFS function is the range to be summed is called *sum_range*. The remaining arguments (are called *criteria_ranges*) can be 1 to 127 range/criteria pairs that determine which values to be summed in the sum_range. That means we can specify 127 pairs of criteria_range and criteria_arguments in the SUMIFS function.

(04) While using the Excel SUMIFS function, we should take care that *sum_range* and each *criteria_range* must have the same number of rows and columns. Otherwise, the SUMIFS formula returns an error.

(05) Excel SUMIFS function only follows the AND logic, which means all the criteria must be TRUE for the number in the corresponding range to be added.

If we want to pass the OR logic then we should use the SUMPRODUCT formula instead of SUMIFS formula.

(06) Please keep in mind that the sum_range should be numeric and which will return the calculation. If sum_range is the non-numeric (text or Unicode), then the SUMIFS formula returns an error.

(07) The Excel SUMIFS function is an advance Excel function that was first introduced in Microsoft Excel 2007 and continues later on versions of Excel 2010, Excel 2013, Excel 2016, Excel for Office 365 and Excel 2019.

(08) The Excel SUMIFS function supports logical operators when criteria are numeric values (which may be an integer, decimal, date and time).

We should use logical operators within double-quotes.

Logical operators are: “=” (equal to), “<=” (not equal to), “=” (greater than), “<” (less than),”>=” (greater than or equal to), “<=” (less than or equal to), ” ” (space or blanks).

With the help of logical operators, Excel SUMIFS function supports expression and expression should be inside the double-quotes.

For example: “>”&10 or “>10”; “<=”&0 or “<=0” etc.

(09) The Excel SUMIFS function supports wildcard characters (asterisk ‘*‘, question mark ‘?‘) for the partial match when criteria are text strings.

Text strings can be the name (“Apple”, “USA”, “Shreyasi”), weekdays (“Monday”, “Tuesday”, etc.,), months (“January”, “February”, etc.,).

Text criteria (i.e., non-numeric criteria) must be enclosed in double-quotes, but numeric criteria do not require any quotes.

An asterisk character (*) matches any sequence of characters, while a question mark (?) matches any one character.

(10) In the case where we find a literal question mark (?) or an asterisk symbol (*) from a text string, we must use a tilde symbol (~) in front of the question mark (e.g., “~?”) or the asterisk symbol (e.g., “~*”).

(11) The Excel SUMIFS function is case-insensitive, which means the upper case, proper case, and lower case are treated as identical. For example, the text strings “JACKSON”, “Jackson”, and “jackson” will be considered to be equal.

(12) When we want to get the summation values in a range based on a single criterion, then we can use either SUMIF or SUMIFS function. When we allow any of them, we will notice that the order of arguments is different between the SUMIFS function and SUMIF function.

The *sum_range* is the third argument in SUMIF function, whereas the first argument in SUMIFS function.

# II. SYNTAX & ARGUMENTS for the Excel SUMIFS function

The syntax and arguments for the SUMIFS function are:

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

The first 3 arguments (*sum_range*, *criteria_range1*, *criteria1*) are mandatory, additional criteria_ranges and their associated criteria are optional, which means those are used according to the requirement.

• *Sum_range*: [Required] a range of cells containing numbers to be summed.

• *Criteria_range1*: [Required] the first range to be evaluated by the associated criteria (i.e., criteria1). Remember that *criteria_range1* must have the same number of rows and columns as the *sum_range*.

• *Criteria1*: [Required] the first condition to be tested against the values in criteria_range1. Criteria can be a number, logical expression, cell reference, text, date, blanks or another Excel function. For example, 50, “>=50”, A1, “Jackson”, “10/10/2019”, “”, or TODAY().

• *Criteria_range2, … criteria_range127:* [Optional] these are additional ranges. We can use up to 127 ranges in the SUMIFS formula. Remember that all *criteria_ranges* must have the same number of rows and columns as the *sum_range*.

• *Criteria2, … criteria127:* [Optional] these are additional criteria associated with the criteria_range and to be tested against the values in criteria_range. Thus, we can use up to 127 criteria in the SUMIFS formula.

# III. What is the Difference between SUMIF and SUMIFS function?

Both the functions are statistical functions and both functions used to sum the values in a range based on specified criteria. However, both functions have distinguished differences:

(01) Excel **SUMIFS** function returns the sum of the cell values in a range that meets one specified criterion (single criteria) or more specified criteria (multiple criteria).

Whereas Excel **SUMIF **function returns the sum of cell values in a range that meets only one specified criterion (single criteria).

(02) The *sum_range* is the first argument in the SUMIFS function, whereas the third argument in the SUMIF function.

(03) In the Excel SUMIFS function, it is mandatory that each criteria_range must have the same number of rows and columns as the *sum_range*. Whereas there is no such mandatory rule in SUMIF function.

# IV. Different Excel SUMIFS Example

## (A) Excel SUMIFS Function with Single Criteria

## (B) Excel SUMIFS Function with Multiple criteria

### ■ Example-1: Excel SUMIFS with Date Range (Manual Way)

(01) Placed an equality sign (=) in cell M3 and type SUM, select SUMIFS from the below suggestion list by Down Arrow key (↓) and press ‘Tab’ key. SUMIFS syntax appears with an open parenthesis.

Note: the upper or lower case does not matter in syntax, excel by default considers it in upper case.

(02) First, select the *sum_range* (should be the value) from the dataset and fix only the columns by pressing three times the F4 key. It seems like $B3:$K3. As a result, the range is fixed and converted from the relative cell reference into the mixed cell reference where columns being fixed. So in that case, when we copy the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $B4:$K4,$B5:$K5, $B6:$K6, $B7:$K7 …so on.

Then place a comma (,) to move to the next argument (i.e., *criteria_range1*).

(03) In the next step, select the *criteria_range1* from the database and fix both the column address and row numbers by pressing the F4 key once. It seems like $B$2:$K$2. As a result, the range has converted from the relative cell reference to absolute cell reference. So, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number do not change.

Then place a comma (,) to move to the next argument (i.e., *criteria1*).

(04) After mentioning the criteria_range1, we move to the *criteria1*. In criteria1, we use an expression like “>=”&”01/05/2019” which means the value should be greater than or equal to the starting day of the month of May’19.

We place the logical operator and date inside the separate double quotation mark and make a bridge between them with an ampersand symbol (&). After that, place a comma (,) to move to the next argument (i.e., *criteria_range2*).

(05) As similar as *criteria_range1*, in the next step, select the *criteria_range2* from the database and fix both the column address and row numbers by pressing the F4 key once. Otherwise, simply copy-paste the *criteria_range1* in place of *criteria_range2*. It seems like $B$2:$K$2.

In Excel terminology, the range is converted from the relative cell reference to absolute cell reference. As a result, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, column address and row number do not change. Then place a comma (,) to move to the next argument (i.e., *criteria2*).

(06) This is an important step. After mentioning the *criteria_range2,* we put *criteria2*.

In the case of *criteria1*, we have considered an expression “>=”&”01/05/2019” which means the value greater than equal to the starting day of the month of May’19. But in the case of *criteria2*, we consider another expression “<=”&”31/05/2019” which means the value less than equal to the ending day of the month of May’19.

Similarly, we should place the condition and date inside the separate double quotation mark and make a bridge between them with an ampersand symbol (&).

Based on the expressions in *criteria1* and *criteria2*, Excel considers the full month of May’19 starting from the 1st day and ends till 31st. Based on the expression, the Excel SUMIFS function sums the value from the sum_range and returns the result.

(07) Extends the formula right side (same row): Alt+E+S+R / Alt+Ctrl+V+R

Copy (Ctrl+C) the cell with the formula and extend the selection to the right side (same row) **➪** press Alt+E+S+R / Alt+Ctrl+V+R sequentially, which will select the ‘Formulas and number formats‘ option under the ‘Paste Special’ dialog box **➪** then click on ‘OK‘ or press ‘Enter‘.

(08) Correct the Start date and End date within the SUMIFS formula in the adjacent cells:

After extending the formula to the right side, we will find that the same value is shown in all cells because the date ranges within the SUMIFS function have not been changed dynamically. So in that case, we should change the date ranges manually.

Edit the cell N3 under the month Jun’19 via the formula bar or press F2 key **➪** change the start date “01/06/2019” in the place of “01/05/2019” and end date “30/06/2019” in the place of “31/05/2019” (remember that June month has 30 days) **➪** press Enter to accept the formula **➪** will get the correct result (figure as below).

Similarly, Edit the cell O3 under the month Jul’19 via the formula bar or press F2 key **➪** change the start date “01/07/2019” in the place of “01/05/2019” and end date “31/07/2019” in the place of “31/05/2019” (remember that July month has 31 days) **➪** press Enter to accept the formula **➪** will get the correct result (figure as below).

(09) Extends the formula to the entire range: Alt+E+S+R / Alt+Ctrl+V+R

After making changes, copy the selection with Excel shortcut Ctrl+C ➪ press sequentially Alt+E+S+R/Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK‘ or press ‘Enter‘

Finally, get the result (SUM value) across the entire selection.

### ■ Example-2: Excel SUMIFS with Date Range (Dynamic Way: using the Cell Reference)

If we want that Excel SUMIFS functions worked dynamically, any changes in the database to be captured and changed accordingly, then we should arrange our database/summary report accordingly.

(01) Please note that we should not type month names because Excel considers them as ‘text format‘ likes **May-19**, **Jun-19**, **Jul-19**. Rather, we use the ‘date format‘ and place the first date of every month in the cells like **01/05/2019**, **01/06/2019**, **01/07/19**. If the months are in series (sequentially), then we only put the first date of the starting month in a first cell. Make a selection and then press Alt+E+I+S to open the Series window.

Date Unit by default selected as ‘Day‘ option, but we should change to the ‘Month‘ option. The ‘Step value‘ must be 1. Press Enter or click on OK.

(02) In the very next step, we should change the format into mmm-yy. Make a selection ➪ press Ctrl+1 to open the ‘Format Cells’ dialog box ➪ Choose ‘Custom‘ category under ‘Number’ Tab ➪ type mmm-yy under ‘Type:’ box ➪ finally, press Enter or click on OK.

(03) After that, place an equality sign (=) in cell M3 and just type ‘sum…‘, then select SUMIFS from the below suggestion list by using the Down Arrow key (↓) and press the ‘Tab‘ key. The SUMIFS syntax appears with an open parenthesis.

Please note that the uppercase or lowercase does not matter at all, Excel by default considers the syntax in upper case.

(04) First, select the *sum_range* (should be the value) from the dataset and fix only the columns by pressing three times the F4 key. It seems like $B3:$K3. As a result, the range is fixed and converted from the relative cell reference into the mixed cell reference where columns being fixed. So in that case, when we copy the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $B4:$K4, $B5:$K5, $B6:$K6, $B7:$K7 …so on.

Then place a comma (,) to move to the next argument (i.e., *criteria_range1*).

(05) In the next step, select the *criteria_range1* from the database and fix both the column address and row numbers by pressing the F4 key once. It seems like $B$2:$K$2. As a result, the range is converted from the relative cell reference to absolute cell reference. So, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number do not change.

Then place a comma (,) to move to the next argument (i.e., *criteria1*).

(06) After mentioning the *criteria_range1*, we move to the *criteria1*. This step is very crucial.

In *criteria1*, we use an expression like “>=”&M$2 which means the value should be greater than or equal to the cell value M2. Cell M2 contains the date “01/05/2019” and in respect of a month, it is May’19. Therefore, Excel considers the dates (values) which are greater than or equal to the starting day of the month of May’19.

Fix the cell reference by pressing twice the F4 key. As a result, the cell reference is converted from the relative cell reference into the mixed cell reference where the row number is absolute but the column address is relative. So in that case, when we copy the formula vertically (column-wise) to the other ranges, row number remains fixed but the column address simultaneously changed.

Please note that we place the logical operator inside the double quotation mark, but there was no need for a quotation mark in the case of a cell reference. However, we make a bridge between them with an ampersand symbol (&). Then place a comma (,) to move to the next argument (i.e., *criteria_range2*).

(07) As similar as *criteria_range1*, in the next step, select the *criteria_range2* from the database and fix both the column address and row numbers by pressing the F4 key once. Otherwise, simply copy-paste the *criteria_range1* in place of *criteria_range2*. It seems like $B$2:$K$2.

In Excel terminology, the range is converted from the relative cell reference to absolute cell reference. As a result, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, column address and row number do not change. Then place a comma (,) to move to the next argument (i.e., *criteria2*).

(08) After mentioning the *criteria_range2*, we move to the *criteria2*. This is also a very crucial step.

In *criteria2*, we use an expression like “<“&N$2 which means the value should be less than the cell value N2. Cell N2 contains the date “01/06/2019” and in respect of a month, it is Jun’19. Therefore, Excel considers those dates (values) which are less than the starting day of the month of Jun’19, which means the date will be one day before the “01/06/2019” i.e. “31/05/2019.

As similar as criteria1, fix the cell reference by pressing twice the F4 key. As a result, the cell reference is converted from the relative cell reference into the mixed cell reference where the row number is absolute but the column address is relative. So in that case, when we copy the formula vertically (column-wise) to the other ranges, row number remains fixed but the column address simultaneously changed.

Based on the expressions in *criteria1* and *criteria2, *Excel considers the full month of May’19 starting from the 1st day and ends till 31st. As a result, the Excel SUMIFS function sums the value from the sum_range and returns the result.

Please note that we place the logical operator inside the double quotation mark, but there was no need for a quotation mark in the case of a cell reference. However, we make a bridge between them with an ampersand symbol (&).

(09) Extends the formula right side (same row): Alt+E+S+R / Alt+Ctrl+V+R

Copy (Ctrl+C) the cell with the formula and extend the selection to the right side (same row) **➪** press Alt+E+S+R / Alt+Ctrl+V+R sequentially, which will select the ‘Formulas and number formats‘ option under the ‘Paste Special’ dialog box **➪** then click on ‘OK‘ or press ‘Enter‘.

After extending the formula horizontally, we find that the last cell value becomes zero. If we focus on formula carefully, the last expression (“<“&P$2)picks the empty cell adjacent to the last non-empty cell.

(10) In the blank cell P2, we put the first day of the next month mentioned in the previous column. The date is “01/08/2019” and in respect of a month, it is Aug’19.

The SUMIFS formula by default recalculates and returns the result.

(11) Extends the formula to the entire range: Alt+E+S+R / Alt+Ctrl+V+R

After making changes, copy the selection with Excel shortcut Ctrl+C ➪ press sequentially Alt+E+S+R/Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK‘ or press ‘Enter‘

(12) This step is optional. If required, we can easily hide the column P by using the Excel shortcut or by using the white text formatting.

• Hide the column by using the Excel Shortcut: Place the cursor anywhere in the column P **➪ **then press Ctrl+Spacebar to select the entire column **➪ **then press Ctrl+0. As a result, column P hides.

• Hide the column by using the ‘White Text Formatting’: Select the cell P2 **➪ **then select the white font color, as a result, text hides behind the same theme color.

### ■ Example-3: Excel SUMIFS with Date Range (Dynamic Way: using the formula EOMONTH)

In this tutorial, we use the EOMONTH function to make the dynamic SUMIFS formula.

The EOMONTH() function (short for End of Month) calculates the last day of any month in any year.

When using EOMONTH(), we need to supply two parameters: the starting date and the number of months we want to look into the future.

• Syntax of EOMONTH() Function:

=EOMONTH (start_date, *number_of_months*)

(i) If we want to find the last day in the current month, just specify 0 for the second parameter.

(ii) If we want to find the last day in the next month, just specify positive numbers for the second parameter (like 1 indicates the end day of next month; 2 indicates end day after two months).

(iii) If we want to look into the past, use a negative number for the ‘number of months’ (like -1 to go back a previous month, −2 to go back in time 2 months).

Steps to Start:

(01) In this section of the Excel SUMIFS function, we use the EOMONTH function in a cell and using this cell reference into the SUMIFS formula.

We place the EOMONTH function in cell M1 to get the last date. Using the cell reference M3 in place of *start_date* (first argument of EOMONTH function) where we already mentioned a date, i.e., 01-May-19.

In place of *months (*second argument), using zero(0) to get the end date of this month.

=EOMONTH(M3, 0)

As a result, we get the month-end date in number format.

We can use the Excel shortcut Ctrl+Shift+# to change the number format into a valid date format.

(02) Extends the formula right side (same row): Alt+E+S+R / Alt+Ctrl+V+R

In the next step, we can extend the formula from one cell to other cells on the right side by using any of the following steps:

• Copy the cell M1 by using Excel shortcut Ctrl+C ➪ make a selection to the right side till the area want to spread the formula ➪ press sequentially Alt+E+S+R/Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK‘ or press ‘Enter‘.

• Alternatively, Copy the cell M1 by using Excel shortcut Ctrl+C ➪ make a selection to the right side till the area want to spread the formula ➪ press Ctrl+R to copy the formula right side.

• Alternatively, Copy the cell M1 by using Excel shortcut Ctrl+C ➪ make a selection to the right side till the area want to spread the formula ➪ simply paste by using Ctrl+V. Remember that as a result, the same cell formatting is applied to the range along with the formula.

(03) Using the Cell Reference:

It is an important step to apply the cell reference within the formula. As a result, cell ranges and cell references are fixed according to their usage in the formula. So, the formula can easily handle to copy in any direction rather than applying the different formulas in different cells.

• Edit the cell by pressing the F2 key or edit through the formula bar.

• Select the *sum_range* B4:K4 ➪ press three times the F4 key and it looks like $B4:$K4.

As a result, the range is converted from the relative cell reference into the mixed cell reference where columns being fixed. So in that case, when we copy the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $B5:$K5, $B6:$K6, $B7:$K7 …so on.

Then place a comma (,) to move to the next argument (i.e., *criteria_range1*).

• Select the *criteria_range1* B3:K3 ➪ press once the F4 key and it looks like $B$3:$K$3.

As a result, the range is converted from the relative cell reference to absolute cell reference. So, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, __columns address and row number do not change__.

Then place a comma (,) to move to the next argument (i.e., *criteria1*).

• In *criteria1, *we use an expression with cell reference like “>=”&M3 which means we consider the value should be __greater than or equal to the cell value M3__. Cell M3 contains the date “01/05/2019” and in respect of a month, it is May’19. Therefore, Excel considers the dates (values) which are __greater than or equal to the starting day of the month of May’19__.

__ Fix the cell reference by pressing twice the F4 key and it looks like “>=”&M$3.__

As a result, the cell reference is converted from the relative cell reference into the mixed cell reference where the row number is absolute but the column address is relative. So in that case, when we copy the formula vertically (column-wise) to the other ranges, __row number remains fixed but the column address simultaneously changed__.

Please note that we place the logical operator inside the double quotation mark, but there was no need for a quotation mark in the case of a cell reference. However, we make a bridge between them with an ampersand symbol (&).

Then place a comma (,) to move to the next argument (i.e., *criteria_range2*).

• As similar as *criteria_range1*, select the *criteria_range2* B3:K3 ➪ press once the F4 key and it looks like $B$3:$K$3.

As a result, the range is converted from the relative cell reference to absolute cell reference. So, when we copy the formula horizontally (row-wise) or vertically (column-wise) in any direction, __columns address and row number do not change__.

Then place a comma (,) to move to the next argument (i.e., *criteria2*).

• In *criteria2, *we use an expression with cell reference like “<=”&M1 which means we consider the value should be __less than or equal to the cell value M1__. Cell M1 contains the last date of the month with respect to the mentioned month in M3 i.e., “31/05/2019”. Therefore, Excel considers all the dates (values) which are __less than or equal to the last day of the month of May’19__.

__ Fix the cell reference by pressing twice the F4 key and it looks like “>=”&M$1.__

As a result, the cell reference is converted from the relative cell reference into the mixed cell reference where the row number is absolute but the column address is relative. So in that case, when we copy the formula vertically (column-wise) to the other ranges, __row number remains fixed but the column address simultaneously changed__.

Please note that here we place the logical operator inside the double quotation mark, but there was no need for a quotation mark in the case of a cell reference. However, we make a bridge between them with an ampersand symbol (&).

• At last, press Enter. Excel by default accepts the conditions and closes the last parenthesis as well.

Excel SUMIFS function returns a sum value between the dates “01/05/2019” and “31/05/2019”.

(04) Extends the formula to the entire range: Alt+E+S+R / Alt+Ctrl+V+R

After making changes, copy the selection with Excel shortcut Ctrl+C ➪ press sequentially Alt+E+S+R/Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK‘ or press ‘Enter‘

The SUMIFS function in Excel works in a dynamic way and returns the result in an extended range based on the supplied criteria.

### ■ Example-4: Excel SUMIFS with Date Range (Dynamic Way: EOMONTH function integrated with SUMIFS formula)

Here we integrate the EOMONTH() function within the SUMIFS function to extract the summation value in the date range. As a result, the SUMIFS formula works more dynamically.

• In cell M4, we write the formula

=SUMIFS($B4:$K4,$B$3:$K$3,“>=”&M$3,$B$3:$K$3,“<=”&EOMONTH(M$3,0))

#### Latest posts by Sudipta Mondal (see all)

- Excel SUMIFS Function - October 27, 2019
- How to use INDEX MATCH Function in Excel with 04 Best Examples - October 13, 2019
- 09 Steps to Reduce Excel File size || Evaluate Existing Formulas || - September 5, 2019

##### Excel SUMIFS Function

Excel SUMIFS function is a statistical function that returns the sum of the cell values in a range that meets one specified criterion (single criteria) or