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.
Download the SUMIFS and DSUM workbook
The sample file has three worksheets. Each contains an example for the three subtopics #1, #2 and #3.
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).
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]
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.
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]
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.
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 expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
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.
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.