Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]
We know how to use SUMIFS function to answer questions like, “What is the sum of values for ‘A’?” But how would you answer questions like,
 What is the sum of values that are neither “A” nor “B”?
We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.
You can use either DSUM or SUMPRODUCT to answer questions like this.
Using DSUM function to sum up neither “A” nor “B” values
Check out below video to understand how to use both DSUM & SUMPRODUCT formulas to calculate sum of neither “A” nor “B” values.
You can also watch this video on our YouTube Channel.
Download Example Workbook
Click here to download example workbook for this video. Play with the formulas to learn more.
More formula examples:
Check out below formula examples to up your Excel game.
 Writing Either Or formulas in Excel
 XOR condition in Excel
 Filtering values that meet multiple conditions
 Introduction to SUMIFS, SUMPRODUCT formulas
Don’t be a beginner OR newbie, master Excel with this awesome program
Excel skills can help you immensely at work and make you popular. So why struggle as a beginner. Become awesome in Excel today with our Excel School program. This comprehensive online course will teach you,
 Data analysis with Excel formulas
 Visual analysis with Charts
 Interactive analysis with Pivot tables, form controls
 Management / Exec reporting with Excel dashboards
 Productivity with key board shortcuts & ninja tricks
Check out Excel School program page for full details and enrollment options.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.

Leave a Reply
« Generating sequence numbers from cluster values [VLOOKUP to the rescue]  What is the sum of values excluding items on stop list? [home work] » 
10 Responses to “Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]”
Hi ,
The SUMIFS usage seems more intuitive than the SUMPRODUCT one , at least in this case.
=SUMIFS(data[Hours],data[Sub Type],L6,data[Sub Type],M6)
=SUMIFS(data[Hours],data[Type],K6,data[Sub Type],L6,data[Sub Type],M6)
make use of the criteria as specified.
Cool. I didn't know we can use same column twice in SUMIFS like this. Surely, this is a better way to solve the problem. Thanks for sharing.
Hi, has Pivots and Tables replaced quite a few "old world" formulas?
Dear Chandoo,
When you have tables as you have here, cant this be better and done faster with just one formula  subtotal 109. You just have to select your preferences from the drop down menu.
Hi, another solution is: using Pivot :)))))) Regards Stef@n
Hi,
Thanks Chandoo. Very useful formula. But i have one question that i want to sum which is blank in sub type by DSUM function. How can i do it?
Blanks (empty cells) in Excel are referenced using double quotes  ""
I'm not terribly familiar with DSUM, but most likely the value you would want in the box is:
=""
Since the syntax looks like [Logical Operator][Logical Test]
Note that this should only catch things that are actually blank. If your system prints a space or nonbreaking space, you'd need to either add a test column to determine whether to include something or use multiple instances of DSUM/SUMIFS.
If I was going to want to use this as a replacement for SUMIFS, I'd want to be able to easily set new logical tests on each row. Given that this requires a title row + a test condition row for each formula instance, that doesn't seem very possible.
Is there a way to have a set row for the Condition titles and then to pull the logical condition from a row below that?
It can be done with simple sumif formula:
=SUMIF(data[Sub Type],"",data[Hours])
SUMPRODUCT paired with MATCH avoids the need to repeat ranges while allowing for any number of possible "nor" values.
=SUMPRODUCT(E6:E20,ISNA(MATCH(D6:D20,{"New","Old"},0)))
=SUMPRODUCT(E6:E20,ISNA(MATCH(D6:D20,{"New","Old"},0))*(C6:C20="Regular"))
As an aside, a simple SUMIF or SUMIFS using "" works, but only because Chandoo's example uses only New, Old, and blank as the Sub Type values. If there were other possibilities, then more explicit formulas would be better.