Excel Tutorial: Using SUMIFS and DSUM in Excel


Summary: Let’s learn how to use the SUMIFS and DSUM functions in Excel. These functions both help us to add numbers in a table that meet specified criteria.

We’ll see why DSUM is easier to use than SUMIFS when dealing with multiple constraints.

Excel functions used in this article: SUMIFS, DSUM.

Difficulty: Intermediate


Download the SUMIFS and DSUM workbook

[Image] Download WorkbookDownload the sample file to follow along with this article.
SUMIFS and DSUM.xlsx (14Kb)

The sample file has three worksheets. Each contains an example for the three subtopics #1, #2 and #3.


Click on the links below to learn more…

#1 – SUMIFS – adding cells that meet specified criteria in different columns
#2 – Limitations of SUMIFS – what happens for multiple criteria in the same column
#3 – DSUM to the rescue – use this database function to handle multiple criteria in the same column
#4 – About the authors – Excel experts for data analytics, BI and reporting

#1 – SUMIFS – adding cells that meet specified criteria in different columns

» Back to contents


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

Sum_range – Range of cells to add.

Criteria_range – The range that is tested using its corresponding criteria.

Criteria – The conditions which define the cells in corresponding criteria range.

Just as the name suggests SUMIFS adds the cells specified in “sum_range” that meet all the criteria specified in “criteria_range1” and “criteria1”, “criteria_range2” and “criteria2” etc.

The first set of criteria is mandatory. The second, third and more are optional.

Each additional range must have the same number of rows and columns as the sum_range.

Note: the criteria must be specified in double quotes (e.g. “Apple” or “>10”)


Today we have a fruity example 😊

Let’s try to find out the total Profit for Apple trees that are taller than 10 units of height. The database of fruit tree profits is in the range B6:F12.

We use this formula.

=SUMIFS(F7:F12, B7:B12, "Apple", C7:C12, ">10") 

It returns the value 160 (summation of Profits in F9 and F10).

[IMAGE] SUMIFS example

So far so good… our criteria are specified in different ranges so SUMIFS works well.

[Victor’s note: you can also use SUMIFS if your data is laid out in rows instead of columns. Just specifiy the sum_range and criteria_range as rows instead of columns]


#2 – Limitations of SUMIFS – what happens for multiple criteria in the same column

» Back to contents

Let’s say that you want to find the profit for all apple AND pear trees from the given database. This task can’t be completed by using a single SUMIF function.

As shown in the image below, the usage of SUMIFS to calculate the profit for apple and pear trees returns 0 instead of the correct value of 282.

[IMAGE] Limitation of SUMIFS

If we use DSUM instead, the same task can be accomplished without any hassle.
The formula used in cell C3 is:

=DSUM(B5:F11, "Profit", H5:H7)
[Victor’s note: you could use two SUMIF functions in one formula to calculate this total like this: =SUMIFS(F6:F11,B6:B11,”Apple”)+SUMIFS(F6:F11,B6:B11,”Pear”) … but then the formula is longer than using one DSUM function and gets unwieldy if you try doing three or more fruits]

#3 – DSUM to the rescue – use this database function to handle multiple criteria in the same column

» Back to contents


DSUM(database, field, criteria)

Database – The range of cells that holds the data, and includes the header row.

Field – This can be either a column label (text string) or a number that indicates the position of a column in the database range.

Criteria – The range of cells that contains the conditions required.

Note: Make sure that the criteria are given as a range of cells and not as a direct input in the formula syntax. This is different from SUMIFS where you can just type in the criteria in the formula.

Here, we will calculate the total profit for apple trees taller than 12 units and orange trees taller than 10 units.

[IMAGE] DSUM formula

This DSUM formula gives the correct value of 250.


We can conclude by saying that if there are multiple criteria in one data column, DSUM is more usable than SUMIFS.

[Victor’s Note: Remember that DSUM does require a separate range of cells to define the sum criteria, whereas SUMIFS can work with a single in-cell formula that doesn’t need a separate range of cells to define the sum criteria]


#4 – About the authors – Excel experts on data analytics, BI and reporting services

» Back to contents

This article was contributed by Perceptive Analytics. Nagendra Kumar, Ritwick P Rao, Jyothirmayee Thondamallu and Chaitanya Sagar contributed to this article.

Perceptive Analytics provides Excel Expert, data analytics, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Its client roster includes Fortune 500 and NYSE listed companies in the USA and India.

[IMAGE] Victor Smile Left Bright

Note from Victor

I’d like to thank Perceptive for writing this tutorial… and if this article got you excited and you want to learn more Excel topics just post your requests below in the comments.

Victor Chan (Launch Excel)

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers


Recent Posts
Showing 5 comments
  • Irina Myagkaya

    valuable stuff

  • Alsayed

    what should i do if i want to use DSUM with multiple rows criteria?
    its like to applying OR logic between 5 rows and 5 columns of criteria !

    appreciate your quick response,




  • Dale

    I have attempted to use dsum in the past but have always found it to be disfunctional in various ways.
    It works but I never use it because their are better tools (imo).
    Having a third location to keep track of is not the worst of it. In particular, when using formulas to make processing easier, dsum lets me down.
    I had to the data provided to actually think of what I would normally go with. The solutions that came to mind formula wise were ‘sum of sumifs’ and sumproduct. Really though, filtering, ‘create table’ or ‘insert pivot table’ with or withut slicers are so convenient it becomes difficult to think of times when one would resort to dsum.

    Good to remember it is there though, just in case.

    Cheers, Dale

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.