• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Excel Formula -DSum formula

Hi, I am using a DSUM formula to add the amounts from CompanyA if it matches Cell A1 and A2. How can I modify the formula so that it only adds the amounts if the amouts are positive ? Below is a sample. On the last row I have the formula that I am using.

    A                             C                D              E           F  
1CmbGrp                  Current            Want
2US                              
3            CmbGrp      CompanyA            
4                  100            
5            US      300            
6                  -200            
7                  300            
8            US      -100            
9Total Positive US $                  200            300
10                              
11                              
12            DSUM(D3:E8,2,A1:A2)
0
Conernesto
Asked:
Conernesto
  • 4
  • 4
1 Solution
 
SteveCommented:
Could you post in a dummy workbook.

Have you considered SUMIFS (2007+) or SUMPRODUCT (2003&Earlier)

Attached example shows simple use of >0 for positive sum.
Example.xlsx
Example.xls
0
 
ConernestoAuthor Commented:
Attached is a dummy workbook. The answer that I want in cell D9 is 300

conernesto
Dsum.xlsx
0
 
SteveCommented:
Here you go.. with 2 possibilities...

=DSUM(C3:D8,2,A1:B2)
With new criteria in B1:B2

=SUMIFS(D3:D8,C3:C8,A2,D3:D8,B2)
No need for criteria headings and not  such a rigid structure as DSUM
Dsum.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ConernestoAuthor Commented:
This looks like it will work. I will close this question after I try on my worksheet.

Thank you.

conernesto
0
 
SteveCommented:
You can replace the cell reference to the greater than zero with ">0" (including quotation marks) in the SUMIFS
0
 
ConernestoAuthor Commented:
Do I replace  ">0" in the formula? If yes, how?
0
 
SteveCommented:
The formula would become:

=SUMIFS(D3:D8,C3:C8,A2,D3:D8,">0")

or
=SUMIFS(D3:D8,C3:C8,"US",D3:D8,">0")
Dsum.xlsx
0
 
ConernestoAuthor Commented:
OK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now