Excel Tutorial: Using SUMIFS and DSUM in Excel

[IMAGE] SUMIFS-and-DSUM

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.

Contents

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

SYNTAX:

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

EXAMPLE:

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

SYNTAX:

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.

EXAMPLE:
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.

=DSUM(B4:F11,"Profit",H4:I6)

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.

Cheers,
Victor Chan (Launch Excel)

Victor Chan
Victor has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with MEng in Manufacturing Engineering from the University of Cambridge. He's on a mission to help you master Excel and VBA with Launch Excel.
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts
Showing 2 comments
  • Irina Myagkaya
    Reply

    valuable stuff

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.