## 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.

Difficulty: Intermediate

SUMIFS and DSUM.xlsx (14Kb)

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

## Contents

#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).

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.

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.

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

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.

## Note from Victor

Cheers,
Victor Chan (Launch Excel)

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!

## GET BETTER AT EXCEL

Recent Posts
• Irina Myagkaya

valuable stuff

• Victor Chan

You’re welcome Irina

• 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 !