Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Dsum For 2 Different Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dsum For 2 Different Criteria

    Hi all,

    I am trying to use DSUM to check 2 different columns of criteria before summing the corresponding data. SUMPRODUCT works great but the data is just too large and the slowdown is unbearable. I have taken a sample off the forum with a simplar problem and worked off it to illustrate what I hope to achieve. In this case, the idea is to determine the sum of the weights for individual names whose height is greater than 5.

    The previous example using Sumproduct and DSUM with the entire criteria listed in L1 to M7 is displayed at the top. However, I am hoping to create a formula which I can copy and paste down as new names and new data gets added. I12 shows a working example, but the name criteria has to be locked (H$12) before it works correctly. Ideally, it should be condensed into a DSUM like I19 which references I18 (criteria), and is then pasted down.

    Any idea how to work around this?

    Thanks alot!
    Attached Files

  • #2
    Re: Dsum For 2 Different Criteria

    Hi,

    Try,

    =DSUM($B$2:$D$44,$D$2,$G$2:H3)-SUM($I$2:I2)

    where G2 houses name and h2 houses the weight.

    See the attachment.
    Attached Files
    Kris

    ExcelFox

    Comment


    • #3
      Re: Dsum For 2 Different Criteria

      Hi Krishnakumar, thanks for the prompt reply. I notice that the solution you proposed subtracts it from the sum of the values. However, the result is that if you try repeating any of the values in G3 to G5 you get 0 for the second instance.

      I was hoping there would be a work-around for tweaking the formula in I26, and I27 to I29 to get the corresponding values for A, B and C. Ideally, if you change all 3 to A you should get the same result for all 3.

      Any suggestions?

      Comment


      • #4
        Re: Dsum For 2 Different Criteria

        How about a pivot table and group the Height.

        See the attachment.
        Attached Files
        Kris

        ExcelFox

        Comment


        • #5
          Re: Dsum For 2 Different Criteria

          Unfortunately its either SUMPRODUCT or DSUM if possible. I need to do this for various stuff, and this is just the general idea. The data is really huge, so implementing SUMPRODUCT is really painfully slow. DSUM seems alot lighter. I really don't want to clutter the worksheets with specific criteria for each field so the ideal would be as per the example in the workbook, with the criteria hidden after that.

          Comment


          • #6
            Re: Dsum For 2 Different Criteria

            You might also try the Data Table.

            See : http://support.microsoft.com/default...b;en-us;282851

            See the attachment.

            HTH
            Attached Files
            Kris

            ExcelFox

            Comment


            • #7
              Re: Dsum For 2 Different Criteria

              Hi Krishnakumar, thanks again for this. Its useful but it still involves a separate detailed criteria table. Is there absolutely no way to condense the criteria into a 2 Row 1 Column table that can be placed just above each column of data that requires a separate DSUM?

              For my data, the criteria for each column (usually 2 or more different criteria) is generally the same but, as with the example, it needs to be broken out by Name (A,B,C...).

              I have different criteria in various columns and it doesn't make sense to create a sprawling set of criteria tables for each. Ideally it should be condensed into a 2Row 1Col table just above the first row in each column that can then be hidden. Many thanks again!

              Comment


              • #8
                Re: Dsum For 2 Different Criteria

                If the 3 given options didn't work for you, then as you said, go with SUMPRODUCT.
                Kris

                ExcelFox

                Comment


                • #9
                  Re: Dsum For 2 Different Criteria

                  Thanks again Krishnakumar, really appreciate all your suggestions. If anyone has any other solution for this, please let me know. Thanks!

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X