Excel Tutorial: Using SUMIFS and DSUM in Excel
Posted on September 1, 2018 By Victor Chan
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
The sample file has three worksheets. Each contains an example for the three subtopics #1, #2 and #3.
Download the sample file to follow along with this article.
SUMIFS and DSUM.xlsx (14Kb)
#1 – SUMIFS – adding cells that meet specified criteria in different columns
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
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
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
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.
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)
Related Posts:
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.