We help IT Professionals succeed at work.
Get Started

sumif with 2 conditions

1,140 Views
Last Modified: 2012-05-09
Hello:
I have the following spreadsheet:
Batch NumberAccount No      Description           Project ID   Type      Amount      Journal Reference
      01-50-00      Special Membership   5725    Credit      $75.00       Sales
      01-50-00      Child Membership       5720    Credit      $25.00       Sales
      01-50-00      Renewal Membership5720    Credit      $12.50       Sales
      01-30-00      Workshop                   1580          Credit      $40.00       Sales
      01-30-00      Custom Program        1580          Credit      $6.00       Sales
      01-20-00      Prior deposit              1302           Debit      $12.35       Sales
      01-40-00      Guided Tour                    1302           Credit      $7.75       Sales
      01-40-00      Adult                   1010     Credit      $17.10       Sales
      01-40-00      Children                   1010           Credit      $11.70       Sales
      01-10-00      Cash                  Debit      $11.70       Sales Deposit
      01-10-00      American Express            Debit      $122.65       Sales Deposit
      01-20-00      Visa/MC/Discover                    Debit      $48.35           Sales Deposti
using vba I need to add the amount field if the project codes are the same and also if the type (Debit/Credit is the same.  My problem was that  with the sumif, I was able to set only one condition (it was adding when the project id was the same but it wasn't useful since it was adding Debits and Credits eg. Prior Deposit and Guided Tour which were supposed to be a different criteria.  I have to have the formula in vba. I have the following formula which did not work:
Total = Application.WorksheetFunction.SumIfs(ActiveSheet.Range("F2:F100"), ActiveSheet.Range("D2:D100"), "=", ActiveSheet.Range("E2:E100"), "=")

Any help would be appreciated
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE