Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Dsum For 2 Different Criteria

1. Member
Join Date
17th November 2005
Posts
20

## 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!

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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.

3. Member
Join Date
17th November 2005
Posts
20

## 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?

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Dsum For 2 Different Criteria

How about a pivot table and group the Height.

See the attachment.

5. Member
Join Date
17th November 2005
Posts
20

## 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.

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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

7. Member
Join Date
17th November 2005
Posts
20

## 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!

Excel Video Tutorials / Excel Dashboards Reports

8. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Dsum For 2 Different Criteria

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

9. Member
Join Date
17th November 2005
Posts
20

## 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!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno