Tutorial 01: How to Use Excel SUMIFS Function with Single & Multiple Criteria

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 (e.g. 50, 75 etc), logical expression ( “>”, “<“, “>=”, “<=”), cell reference (A1, M1, O21 etc.), text (“Jackson”, “Shreyasi”), date ( “10/10/2019”), blanks (“”) or another Excel function [TODAY(), EOMONTH()].

(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 the 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 advanced Excel function that was first introduced in Microsoft Excel 2007 and continues later on in 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, the 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 must 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 & 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) USE OF WILDCARDS IN EXCEL SUMIF & SUMIFS FUNCTION

Wildcard characters such as asterisk ‘*’ and question mark ‘?’ can be used within the criteria argument when using the SUMIFS function or SUMIF function. The wildcards will help us in finding matches in a range that are similar but not accurate matches.

(01) Asterisk (*) – It matches any sequence of characters. It can be used after, before, or surrounding criteria to allow partial search criteria to be used.

For example, it is used in the criteria of both SUMIFS function and SUMIF function:

M0* – It implies all cells in the range that start with M0

*01 – It implies all cells in the range that ends with 01

*M0* – Cells in the range that contain M0; *01* – Cells in the range that contain 01; 

(02) Question mark (?) – It matches any single character.

For example, 

?001 – Here “?” will take the place of a single character. It will match with M001, S001, etc.

M00? – It will match with M001, M002, M003, etc.

S?N – it will match with SUN, SON, SEN, etc. However, it will not take into consideration Name (like SUNLIGHT, SUNRAYS, SUNSHINE, etc.)

Q. What if the given data contain an asterisk or an actual question mark?

In this case, we can use a tilde (~) symbol. We need to type “~” in front of the question mark or asterisk symbol in that scenario.

(V) ERROR IN EXCEL SUMIF & SUMIFS FUNCTION?

We get an error from the Excel SUMIFS or SUMIF function is #VALUE! error:

#VALUE!  –  if the supplied sum_range and criteria_range arrays do not have equal length.

(VI) DIFFERENT EXCEL SUMIFS EXAMPLE

A. Excel SUMIFS or SUMIF Function with Single Criteria

Both Excel SUMIFS function and SUMIF function give the same result on single criteria. We Explain both functions simultaneously to get the same result. 

In the following example, based on the list in cells A3:C21, to find out sales value (summation value) against the Code, which means against the single criterion.  

Explanation: 

(01)  In Example1, we find out the summation value of Code M001 mentioned in cell E4 from the range of a dataset, i.e., A3:C21 with the help of SUMIFS and SUMIF function accordingly.

We get the same result of 10.08 in both cases. 

(02)   In the Example2, we find out the summation value of Code criteria ‘M0’ from the range of dataset, i.e., A3:C21 with the help of SUMIFS and SUMIF function accordingly. ‘M0’ denotes those codes having ‘M0’. Thus, in this case, Excel considers all the Codes M001, M002, and M003.

In the case of partial criteria, we can use the asterisk (*) symbol surrounding the criteria. An asterisk (*) matches a series of zero or more characters. 

We get the same result 54.74 in both cases.  

(03) For the Example3, we find out the summation value of Code criteria ‘01’ from the range of a dataset, i.e., A3:C21 with the help of SUMIFS and SUMIF function accordingly. ‘01’ denotes those codes having ‘01’. Thus, in this case, Excel considers Codes M001 and S001.

In the case of partial criteria, we can use the asterisk (*) symbol surrounding the criteria.

We get a result of 51.04 in both cases.

(04) In Example4, we find out the summation value of Code criteria ‘01’ from the range of dataset, i.e., A3:C21 with the help of SUMIFS and SUMIF function accordingly. ‘01’ denotes those codes having ‘01’. Thus, in this case, Excel considers Codes M001 and S001.

In the case of partial criteria, we can use the question mark (?) symbol. A question mark (?) matches exactly one character. 

We get a result of 51.04 in both cases.

We can use the logical operator in the criteria of both the SUMIFS and SUMIF functions.

(05) In the above Example 5, we use greater than “>” logical operator.

We get the same result 115.17 in both SUMIF and SUMIFS functions.

(06) In the given Example 6, we use less than “<“ logical operator.

We get the same result 21.95 in both SUMIF and SUMIFS functions.

(07) In the given Example 7, we use greater than equal to “>=” logical operator.

We get the same result 120.17 in both SUMIF and SUMIFS functions.

(08) In the given Example 8, we use less than equal to “<=” logical operator.

We get the same result 26.95 in both SUMIF and SUMIFS functions.

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 the ‘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 at all.

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 at all. 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 to the Right Side (Row wise or Horizontally): 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) Change the Start Date and End Date within the SUMIFS formula in the Adjacent Cells (Row wise / Horizontally):

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

Edit the cell N3 under the month Jun’19 by clicking on it or via the formula bar or press the 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 ➪ finally, get the correct result (figure as below).

Similarly, Edit the cell O3 under the month Jul’19 by clicking on it or via the formula bar or press the 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 ➪ finally, 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 (i.e., M3:O20).

➢ Example-2: Excel SUMIFS with Date Range (Dynamic Way: Using of Cell References)

If we want that Excel SUMIFS functions worked dynamically which means any kind of changes in the database has captured, simultaneously the SUMIFS formula has captured the changes as well.

(01) Please keep in mind that we should not type the 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 suggested 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 changed it 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 i.e., B3:K3, 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 column addresses being fixed.

So in that case, while copying 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 i.e., B2:K2, 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, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, column addresses and row numbers do not change at all.

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

(06) This step is very crucial.

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

Select and Fix the cell reference by pressing the F4 key twice, thus it seems like “>=”&M$2. 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, while copying the formula vertically (column-wise) to the other ranges, the row number remains fixed but the column address simultaneously changed.

Please note that we place a logical operator inside the double quotation mark, but there was no need for a quotation mark for 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, i.e., B2:K2, 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 to save time. However, 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, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, column address and row number do not change at all. 

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

(08) As similar to criteria1, the criteria2 is also an important step.

In criteria2, we use an expression like  “<“&N2 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. So, Excel considers the date (value) which is less than the starting day of the month of Jun’19, i.e., one day before the “01/06/2019”, more clearly i.e. “31/05/2019. 

Then select and fix the cell reference by pressing the F4 key twice. 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, while copying the formula vertically (column-wise) to the other ranges, the 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 to the Right Side (Row wise or Horizontally): Alt+E+S+R / Alt+Ctrl+V+R

Copy (Ctrl+C) the cell with the formula and extending the selection to the right side (same row) with the help of Shift + Arrow Keys ➪ press sequentially Alt+E+S+R / Alt+Ctrl+V+R, which allow selecting 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 the formula carefully, the last expression (“<“&P$2) picks the empty cell adjacent to the last non-empty cell.  

(10) In the blank cell P2, place the first day of the next month on the basis of the month mentioned in the previous cell. Thus, the date will be “01/08/2019” and in respect of a month, it will be 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) Hide the column by using the Excel Shortcut: Ctrl+ )

This step is optional. If required, we can easily hide the column P by using the Excel shortcut or by using a similar Font Color according to the background.

• Hide the column by using shortcut Ctrl+): Place the cursor anywhere in the column P ➪ then press Ctrl+Spacebar to select the entire column ➪ then use excel shortcut Ctrl+). As a result, column P hides.

• Hide the text by using the ‘Font Color’: Select the cell P2 or entire column P ➪ then select the similar ‘Font Color‘ according to the background (or, use Excel shortcut Alt+H+F+C sequentially press Alt, H, F, C), as a result, text hides behind the same theme color. In this scenario, our background is white, so we select White Font Color to hide the text. 

➢ 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 (zero) for the second parameter (i.e., ‘number of months’).

(ii) If we want to find the last day in the next month, just specify positive numbers for the second parameter (for example,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 second parameter (for example, -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, then this cell reference is mapped into the SUMIFS formula.

In cell M1, We place the EOMONTH function to get the last date.

However, inside the EOMONTH function, we map 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 the current 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) Extending the formula to the Right Side (Row-wise or Horizontally): Alt+E+S+R / Alt+Ctrl+V+R

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

(i) 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 (by Shift+➡) ➪ then press sequentially Alt+E+S+R  or press Alt+Ctrl+V+R which will allow selecting the option ‘Formulas and number formats’ in the ‘Paste Special’ dialog box ➪ Click on ‘OK’ or press ‘Enter’.

(ii) Alternatively, Copy the cell M1 by using Excel shortcut Ctrl+C ➪ make a selection to the right side till the area wants to spread the formula (by Shift+➡) ➪ press Ctrl+R to copy the formula right side.

(iii) Alternatively, Copy the cell M1 by using Excel shortcut Ctrl+C ➪ make a selection to the right side till the area wants to spread the formula (by Shift+➡) ➪ 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 of Cell Reference:

It is an important step applying the cell reference into the formula. Cell ranges and cell references need to be fixed according to their usage in the formula. So, we can easily handle the formula to copy in any direction rather than applying the different formulas in different cells. 

(i) Edit the cell by clicking on it or pressing the F2 key or edit through the formula bar.

(ii) Then select the sum_range B4:K4 ➪ press the F4 key three times and it seems to be $B4:$K4.

As a result, the range is converted from the relative cell reference into the mixed cell reference where column addresses 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).

(iii) In the next step, select the criteria_range1  B3:K3 ➪ fix both the column address and row numbers by pressing the F4 key once. It seems like $B$3:$K$3. As a result, the range is converted from the relative cell reference to the absolute cell reference.  So, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number remain fixed means they do not change at all.

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

(iv) After fixing the criteria_range1, we need to fix the criteria1. 

In criteria1, we use an expression like “>=”&M$3 which means 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 the F4 key twice. 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, the 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).

(v) In the next step, select the criteria_range2 i.e., B3:K3 and fix both the column address and row numbers by pressing the F4 key once. It seems like $B$3:$K$3.

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

(vi) In criteria2, we use an expression like  “<=“&M$1 which means the value should be less than equal to the cell value M1. Cell M1 contains the last date of the same month mentioned in cell M3 i.e., “31/05/2019”. Therefore, Excel considers the dates (values) which are less than or equal to the “31/05/2019”.

As similar as criteria1, need to fix the cell reference by pressing the F4 key twice. 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. Therefore, when we copy the formula vertically (column-wise), the 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 (&).

Press Enter to apply the formula and Excel by default closes the last parenthesis as well. 

(04) Extending 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’ in the ‘Paste Special’ dialog box ➪ Click on ‘OK’ or press ‘Enter’.

Finally, get the result (SUM value) across the entire selection (i.e., M4:O21).

➢ Example-4: Excel SUMIFS with Date Range (Dynamic Way: EOMONTH Function integrated into Criteria)

(01) Especially while working with the month-wise summation, we use to integrate the EOMONTH function into the Criteria of SUMIFS functions. Both functions create an advanced level of nested formula. 

In cell M4, an advanced SUMIFS formula has been applied with EOMONTH function:

We should know the Syntax for the EOMONTH formula:

(03) Using of Cell Reference:

It is an important step applying the cell reference into the formula. Cell ranges and cell references need to be fixed according to their usage in the formula. So, we can easily handle the formula to copy in any direction rather than applying the different formulas in different cells. 

(i) Edit the cell by clicking on it or pressing the F2 key or edit through the formula bar.

At first, select the sum_range B4:K4 ➪ press the F4 key three times and it seems to be $B4:$K4.

As a result, the range is converted from the relative cell reference into the mixed cell reference where column addresses 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).

(ii) In the next step, select the criteria_range1  B3:K3 ➪ fix both the column address and row numbers by pressing the F4 key once. It seems like $B$3:$K$3. As a result, the range is converted from the relative cell reference to the absolute cell reference.  So, while 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).

(iii) After fixing the criteria_range1, we need to fix the criteria1. 

In criteria1, we use an expression like “>=”&M$3 which means 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 the F4 key twice. 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, the 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).

(iv) In the next step, select the criteria_range2 i.e., B3:K3 and fix both the column address and row numbers by pressing the F4 key once. It seems like $B$3:$K$3.

In Excel terminology, the range is converted from the relative cell reference to the absolute cell reference. As a result, when we copying 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).

(vi) In criteria2, we use the EOMONTH function with a logical operator like less than equal to and make an expression like “<=”&EOMONTH(M3,0).

Remember that we use the EOMONTH function to get the last date of the month according to the suggested date.
The Syntax of the EOMONTH function is as follows:

Start_date may be the date or cell reference (where contains a date into a cell). Here we select the cell M3 because it contains a date “01/05/2019” and in respect of a month, it is May’19.
 
Place a comma (,) to move to the next argument.
 
The last argument of the EOMONTH function is months. We place zero (0) value because we want to find the last day in the current month.
EOMONTH(M3,0)
 
As a result, the EOMONTH function expresses the last date of May’19, i.e., “31/05/2019”.
We use a logical operator less than equal to (“<=”) and make a bridge between them with an ampersand symbol (&). Thus, the expression is like “<=”&EOMONTH(M3,0).

This expression indicates that the value should be less than equal to the last date “31/05/2019”.

Remember that proper use of cell references make any formula to perform dynamically. Select the cell M3 inside the EOMONTH formula and fix the cell reference by pressing the F4 key twice. 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. Therefore, while we copying the formula vertically (column-wise), the 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 the1st day and ends till 31st. As a result, the Excel SUMIFS function sums the value from the sum_range and returns the result.

(04) Extending the formula to the Entire Range: Alt+E+S+R / Alt+Ctrl+V+R

Finally, copy the cell M4 with Excel shortcut Ctrl+C ➪ make a selection of entire range with Shift + Arrow Key(s) ➪ 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’ to accept the formula.

Finally, we get the result (SUM value) across the entire selection (i.e., M4:O21).

(VII) CONCLUSION

01. The SUMIFS function is more powerful than the SUMIF function because it works on both single criteria and multiple criteria as well.
 
02. The SUMIFS function can perform with the wildcards, e.g., an asterisk (*) or a question mark (?).
 
03. The SUMIFS function allows getting the summation values between two dates. On the basis of that, we can calculate the summation between months and years as well.
 
04. The SUMIFS function nested with the EOMONTH function makes a dynamic formula that allows summation of a specific month from a range of dates.
 
05. The dynamic SUMIFS function saves a lot of time for daily Excel users to avoid applying different SUMIFS formula in different cells.