top of page

Microsoft Excel articles et conseils

Excel Icon.png
Back Button

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. 

Sumifs 1.jpg

Step 2. Add the SUMIFS formula to the Sales column for the results in the table below.

Sumifs 2.jpg

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.

Sumifs 3.jpg

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.

bottom of page