Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumifs formula compairing data on different lines
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 he http://freefilehosting.net/download/3mg60 Hutch "Gary" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert SUMIFS formula from 2007 to 2003 | Excel Discussion (Misc queries) | |||
SUMIFs for particular cells in blocks of data | Excel Worksheet Functions | |||
sumifs formula in excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - SUMIFS formula use between tabs | Excel Discussion (Misc queries) | |||
SUMIFS Formula Problem | Excel Discussion (Misc queries) |