sumifs formula compairing data on different lines

[Gary]

Not sure if I am on the correct forum, I am trying to use sumifs to calculate
totals for data on different rows

Type Units DOS Charges Paid Balance

Chg 11.00 06/06/2008 880.00 0.00 0.00
Pmt 06/06/2008 0.00 0.00 0.00
Pmt 06/06/2008 0.00 0.00 0.00
Adj 06/06/2008 0.00 0.00 0.00
Pmt 06/06/2008 0.00 560.00 0.00
Chg 18.00 06/10/2008 1,440.00 0.00 711.00
Pmt 06/10/2008 0.00 729.00 0.00
Chg 16.00 06/11/2008 1,280.00 0.00 1,280.00
Pmt 06/11/2008 0.00 0.00 0.00
Pmt 06/11/2008 0.00 0.00 0.00
Chg 13.00 06/11/2008 1,040.00 0.00 485.00
Pmt 06/11/2008 0.00 0.00 0.00
Adj 06/11/2008 0.00 0.00 0.00
Pmt 06/11/2008 0.00 100.00 0.00

This is a small sample of a report that I am trying to analysis, I am trying
to get a total of all BCBS Payments on accounts that have a 0.00 balance.
This report is about 10,000 lines in excel, too long to manually delete
accounts with a balance

If Chg Balance = 0.00 then total Pmt Paid

Thanks

Gary

 

[Tom Hutchins]

Assuming your sample data is in columns A-F, try

=SUMPRODUCT(--(F3:F10000=0),--(A3:A10000="Pmt"),E3:E10000)

or

{=SUM(IF((F3:F10000=0)*(A3:A10000="Pmt"),E3:E10000,0))}

This second formula is an array formula. After you type it in, press
Ctrl-Shift-Enter instead of Enter. If you do it correctly, Excel will wrap
curly brackets {} around your formula (don't type them yourself.)

Hope this helps,

Hutch

 

[Gary]

Tom,

I tried this and tested by adding a balance to a 0.00 item and balance did
not change, The zero balance is on a different row from the Chg row and the
balance to the account is on the Chg row, the system does not produce a
rolling balance

 

[Tom Hutchins]

I guess I didn't understand exactly what you want. Is this correct: if the
Balance on a Chg row is zero, you want to sum the Paid amounts from the
related Pmt rows? The only ways I see (in your sample data) to relate the
payment rows to a Chg row are by date & proximity on the sheet. Try this
(assuming your data begins in row 2, and the first free column is G):

In G2, enter this formula:
=IF(AND(A2="Chg",F2=0),TRUE,IF(C2=C1,G1,FALSE))
In H2, enter this formula:
=IF(G2=TRUE,IF(A2="Pmt",E2,0),0)

Copy G2 & H2 down through all your rows of data. The sum of column H should
be the total payments for zero-balance accounts.

Hope this helps,

Hutch

 

[Gary]

Getting close, let me explain a little more
Line 1 through line 5 belong to 1 account, the charge amount was 880.00,
they paid 560.00 and adjusted the balance I removed the adjustment column due
to space, that paid amount I want to add. line 6 and 7 go together charge
1440.00 paid 729.00 and has a balance of 711.00, I do not want to add that
paid amount to the total. from Chg to the next chg is 1 account I took out
the account number to save space as well

The correct answer to this example is 560.00, I just need to figure out how
to do this for 15,000 rows


Type Units DOS Charges Paid Balance

1 Chg 11.00 06/06/2008 880.00 0.00 0.00
2 Pmt 06/06/2008 0.00 0.00 0.00
3 Pmt 06/06/2008 0.00 0.00 0.00
4 Adj 06/06/2008 0.00 0.00 0.00
5 Pmt 06/06/2008 0.00 560.00 0.00
6 Chg 18.00 06/10/2008 1,440.00 0.00 711.00
7 Pmt 06/10/2008 0.00 729.00 0.00
8 Chg 16.00 06/11/2008 1,280.00 0.00 1,280.00
9 Pmt 06/11/2008 0.00 0.00 0.00
10 Pmt 06/11/2008 0.00 0.00 0.00
11 Chg 13.00 06/11/2008 1,040.00 0.00 485.00
12 Pmt 06/11/2008 0.00 0.00 0.00
13 Adj 06/11/2008 0.00 0.00 0.00
14 Pmt 06/11/2008 0.00 100.00 0.00

 

[Tom Hutchins]

As far as I can tell, the last set of formulas I sent gives exactly the
output you describe. I have uploaded my test file which has your sample data
+ the formulas I suggested (I did add a second zero-balance account with a
6/13 DOS). You can download it here:

http://freefilehosting.net/download/3mg60

Hutch