Services offered worldwide
or
Your partner in computer software training & application development
for over 25 years
Microsoft Excel
SUMIFS Function
Use SUMIFS to conditionally sum a range of values, based on multiple criteria
Would you like the most current sales figure for a certain product, sold by a particular rep? And perhaps you'd like to limit this to a date range? The SUMIFS function, which was introduced in Excel 2007, makes quick work of this challenging task!
​
In the example below, let's sum the sales for Printers sold by the rep Ferrara.
Step 1. Set up your data correctly, preferably converting it to a table.
Step 2. Add the SUMIFS formula to the Sales column for the results in the table below.
DETAILED EXPLANATION
​
SYNTAX: =SUMIFS(Sum Range,Criteria Range 1,Criteria 1, Criteria Range 2, Criteria 2)
EXAMPLE 1 IN THE IMAGE ABOVE: This formula refers to a regular data range. Notice that we use the F4 function key to render cells absolute. In other words, to 'lock' cells with dollar signs.
Hint: To select a range, click in the first cell under the column heading and then use Ctrl + Shift + ↓ to select the rest of the column cells.
EXAMPLE 2: This example refers to a table range (tables are preferable, as will expand/contract as data is added/removed and it isn't necessary to use absolute cell references):
=SUMIFS(tblSales[Total],tblSales[Product],I6,tblSales[Rep],H6)
Now let's add From and To dates. Notice that we must surround our >= and <= operators with double quotes. We must also 'attach' the cell addresses that contain our dates with an ampersand.
Here is the updated formula. It refers to the Date column in the data table twice; once for the From date and once for the To date:
=SUMIFS(tblSales[Total],tblSales[Product],I6,tblSales[Rep],H6,tblSales[Date],">="&H3,tblSales[Date],"<="&I3)
​
​
​
​
​
​
Give the SUMIFS function a try, and let us know if you have any questions, or would like to arrange training on this
or other Excel functions and features!
© 2018 DJH Training & Application Solutions Inc.