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.
10 Responses to “Sum up neither “A” nor “B” values – How to use DSUM function in Excel ”
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 non-breaking 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.