asked on

sumif with 2 conditions

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

gloriagalvez

ASKER

The Batch number is an empty column, the project ids is the 5725, 5720, etc., the journal reference is the Sales and Sales Deposit...

gtgloner

If you could post the sheet here, that would be helpful. I have a feeling that the SUMPRODUCT function is what's needed. There is an excellent article on the net about how to use that here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

etech0

for sumif with 2 conditions:
=sumif(and(range,first condition,second condition))

etech0

Oops! I meant this:
=sumif(range,and(first condition,second condition))

steverice

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ....)

Look in excel help for an explanation.

Steve

etech0

Mine is for 2 criteria in one range, steverice's is for 2 ranges, each with 1 criteria.

Anthony Perkins

>>Zones: SQL Server 2005, Microsoft Excel Spreadsheet Software, Spreadsheet Software<<
Pray tell how is this even remotely related to MS SQL Server 2005?

steverice

What about concatenating both the project_id and the type into a single column and then using:
=sumif(range,[project_id]&"Debit",amount)-sumif(range,[project_id]&"Credit",amount)

patrickab

gloriagalvez,

Please upload your file. The probelm is easily solved with or without VBA. However please indicate whether it must be part of a VBA macro or whether just Excel formula are OK.

Patrick

gloriagalvez

ASKER

Please see attached file.
to-EE.xls

gloriagalvez

ASKER

sorry acperkins... not related to sql server 2005

patrickab

gloriagalvez,

Use:

=SUMPRODUCT(('Import File'!$D$2:$D$13='Final csv file'!C6)*('Import File'!$E$2:$E$13='Final csv file'!D6)*'Import File'!$F$2:$F$13)

and copy to the cells you need it.

It's in the attached file.

Patrick
Copy-of-to-EE-01.xls

gloriagalvez

ASKER

Hello Patrick:
The formula worked!! yeahh .. but I am trying to use it in VBA and I need the D2 and the E2 to be variables. How would I accomplish that? Please look below:

Total = [SUMPRODUCT(('FE Import File'!$D$2:$D$100='Final csv import file'!D2)*('FE Import File'!$E$2:$E$100='Final csv import file'!E2)*'FE Import File'!$F$2:$F$100)]
Thank you

patrickab

In VBA you would need something like this:

Total.Formula = "=SUMPRODUCT(('FE Import File'!$D$2:$D$100='Final csv import file'!D2)*('FE Import File'!$E$2:$E$100='Final csv import file'!E2)*'FE Import File'!$F$2:$F$100))

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gloriagalvez

ASKER

Hello Patrick:
Maybe I did not explain myself in my last post.. the D2 and the E2 need to be variables. like ActiveSheet.Cells(i + 1, 4) or something like that. After that, the formula needs lo land in the right cell.
So the value of the formula needs to be put into the Total variable.

For i = 1 To LastRow

If rs1 <> rs Then
For j = 1 To 9
Sheets("FE Import File").Select
rs = ActiveSheet.Cells(i, j).Value

If j = 6 And rs <> "Amount" Then
If ActiveSheet.Cells(i, j - 2) <> "" Then
Total = [SUMPRODUCT(('FE Import File'!$D$2:$D$100='Final csv import file'!D2)*('FE Import File'!$E$2:$E$100='Final csv import file'!E2)*'FE Import File'!$F$2:$F$100)]

Sheets("Final CSV import file").Select
ActiveSheet.Cells(i, j).Value = Total

rs1 = ActiveSheet.Cells(i, 2) & ActiveSheet.Cells(i, 4)
Else
Sheets("Final CSV import file").Select
ActiveSheet.Cells(i, j).Value = rs
End If
''Debug.Print ActiveSheet.Cells(i, j).Value
Else
Sheets("Final CSV import file").Select
ActiveSheet.Cells(i, j).Value = rs
End If

Next j

End If
Sheets("FE Import File").Select
rs = ActiveSheet.Cells(i + 1, 2).Value & ActiveSheet.Cells(i + 1, 4)
Next i

gloriagalvez

ASKER

Hello Patrick:
I found a walkaround for my problem.. The formula applied in the context I had gave me a zero value in the target cells. I left it as zero and with code went back and copy the formula on first cell in the range and copy it to the rest of the range.. Thank you.