Extract data from a table using two criteria variables

[Bob789]

Can someone tell me how in Excel, through formulas and/or filters or some other function, I can get the total dollar amount for each charge description for each address with table A being the original source data and table B being the extracted output as shown below. Thank you.



Table A Data




Address
Charge Description
Amount


1303 W Clinton Dr Unit 109
Assessments
245


1303 W Clinton Dr Unit 109
Lockbox Payment
-245


1303 W Clinton Dr Unit 109
Assessments
245


1303 W Clinton Dr Unit 109
Late Fee
10


1303 W Clinton Dr Unit 109
Late Interest
7.35


1303 W Clinton Dr Unit 109
Lockbox Payment
-245


1303 W Clinton Dr Unit 109
Lockbox Payment
-245


1303 W Clinton Dr Unit 109
Assessments
245


1303 W Clinton Dr Unit 109
Late Fee
10


1303 W Clinton Dr Unit 109
Late Interest
3.94


1303 W Clinton Dr Unit 110
Assessments
245


1303 W Clinton Dr Unit 110
Lockbox Payment
-245


1303 W Clinton Dr Unit 110
Assessments
245


1303 W Clinton Dr Unit 110
Lockbox Payment
-245


1303 W Clinton Dr Unit 110
Assessments
245


1303 W Clinton Dr Unit 110
Late Fee
10


1303 W Clinton Dr Unit 110
Late Interest
3.67


1303 W Clinton Dr Unit 110
Lockbox Payment
-245


1303 W Clinton Dr Unit 110
Assessments
245


1303 W Clinton Dr Unit 110
Lockbox Payment
-245







Table B Result




Address
Charge Description
Amount


1303 W Clinton Dr Unit 109
Assessments
735


1303 W Clinton Dr Unit 109
Late Fee
20


1303 W Clinton Dr Unit 109
Late Interest
11.29


1303 W Clinton Dr Unit 109
Lockbox Payment
-735


1303 W Clinton Dr Unit 110
Assessments
980


1303 W Clinton Dr Unit 110
Late Fee
10


1303 W Clinton Dr Unit 110
Late Interest
3.67


1303 W Clinton Dr Unit 110
Lockbox Payment
-980

[大灰狼1976]

Hi Bob789!
Please refer to the Attachment.

[Bob789]

My system will not let me open spreadsheets like this. Can you put this information in the body of the email. Thank you.

[大灰狼1976]

alright!
23744
23745

commandbutton's code on sheet1

Private Sub CommandButton1_Click()
Dim arr, arr1, i&, d As Object, r&, s$
arr = Sheets(1).[a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
ReDim arr1(1 To UBound(arr) - 1, 1 To UBound(arr))
For i = 2 To UBound(arr)
s = arr(i, 1) & "," & arr(i, 2)
If Not d.exists(s) Then
r = r + 1
d(s) = r
arr1(r, 1) = arr(i, 1)
arr1(r, 2) = arr(i, 2)
End If
arr1(d(s), 3) = arr1(d(s), 3) + arr(i, 3)
Next i
With Sheets(2)
.[a1].CurrentRegion.Offset(1).ClearContents
.[a2].Resize(r, UBound(arr1)) = arr1
.Activate
End With
MsgBox "Done!"
End Sub

[Tom Jones]

In column C of Table B Result try:

=SUMPRODUCT(($A$2:$A$21=H2)*($B$2:$B$21=I2)*$C$2:$C$21)

[p45cal]

A pivot table (a very basic/simple to set up one) can do this in a flash:
23749Click on the image to get a full size version.

[Bob789]

P45cal
This is exactly what I need. FYI before you replied I concatenated the charge description to the address and used the sumif formula to get my data although the pivot table is much more versatile due to the ability to sort, query and filter. Thank you very much.