Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

Posted on June 8th, 2016 in Excel Howtos , Learn Excel - 10 comments

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.

data-for-dsum-example

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.

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.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]”

  1. NARAYAN says:

    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.

  2. A Rajan Pradeep says:

    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.

  3. Stef@n says:

    Hi, another solution is: using Pivot :)))))) Regards Stef@n

  4. Ghanshyam says:

    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?

    • Nathan says:

      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.

  5. Nathan says:

    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?

  6. Rudra Sharma says:

    It can be done with simple sumif formula:

    =SUMIF(data[Sub Type],"",data[Hours])

  7. David N says:

    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.

Leave a Reply