Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494


Courtesy of
DJH Training & Application Solutions Inc.

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 the the Sales column to arrive at the results below



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



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.




Do you have questions on how to implement the SUMIFS function in your own work? Feel free to contact us!



More Newsletter Articles

© 2018 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 25 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit