Formula to find the balance of each bank

Status
Not open for further replies.

4hoteliers

New Member
Joined
Jan 18, 2016
Messages
42
Balance.xlsx
ABCDEFGHIJKLM
1AccountPayeeCategoryreconcile or clearPaymentDepositCleared PaymentCleared DepositAccount BalanceCleared BalanceBalanceBANK NAMEEACH BANK balance
2Balance EUROBANKBalance - EUROBANK[Balance - EUROBANKc1500001500015,000.0015,000.0015,000.00 EUROBANK
3Balance ALPHA BAKBalance - ALPHA BANK[Balance - ALPHA BANKc8500085008,500.008,500.0023,500.00 ALPHA BANK
4CashInterestINTEREST - EUROBANKc0.1200.120.120.1223,500.12 EUROBANK
5CashInteest TaxINTEREST TAX - EUROBANKc0.020.0200.100.1023,500.10 EUROBANK
6Web BankingVodafonePhone VODAFONE - EUROBANKc21.6121.610(21.61)(21.61)23,478.49 EUROBANK
7Web BankingShared Utility Monthly BillShared Utility Monthly Bill - ALPHAc39.3339.330(60.94)(60.94)23,439.16 ALPHA
8Web BankingOffice funriture BuyOffice Furniture Buy - EUROBANKc73.7573.750(134.69)(134.69)23,365.41 EUROBANK
9Web BankingSoftware BuySoftware Buy - ALPHAc104.13104.130(238.82)(238.82)23,261.28 ALPHA
10Web BankingCar LeasingCar Leasing - EUROBANKc447.64447.640(686.46)(686.46)22,813.64 EUROBANK
11Client DepositClient 1 DepositTHIRA'S DOLPHIN - EUROBANKc1000010001,000.001,000.0023,813.64 EUROBANK
12CashWithraw cashWithdraw - Eurobankc4004000(399.90)(399.90)23,413.64 Eurobank
13CashWithraw cashWithdraw - ALPHAc2002000(599.90)(599.90)23,213.64 ALPHA
14Web BankingAcount PaymentACCOUNTANT PAYMENT - EUROBANKc286.44286.440(972.90)(972.90)22,927.20 EUROBANK
15Web BankingAnnual AdvertisitngAnnual Advertising Hotelier Academy - EUROBANKc330033000(4,272.90)(4,272.90)19,627.20 EUROBANK
16CashWithraw cashWithdraw - ALPHAc2002000(799.90)(799.90)19,427.20 ALPHA
17Web BankingProfessional chamber paymentProfessional Chamber - EUROBANKc25250(4,297.90)(4,297.90)19,402.20 EUROBANK
18Web BankingElectricity bill paymentElectricity - EUROBANKc420.5420.50(4,718.40)(4,718.40)18,981.70 EUROBANK
Sheet1
Cell Formulas
RangeFormula
G2:G18G2=IF(OR(D2="c",D2="R"),E2,0)
H2:H18H2=IF(OR(D2="c",D2="R"),F2,0)
I2:I18I2=SUMIF(A$1:A2,"="&A2,F$1:F2)-SUMIF(A$1:A2,"="&A2,E$1:E2)
J2:J18J2=SUMIF(A$1:A2,"="&A2,H$1:H2)-SUMIF(A$1:A2,"="&A2,G$1:G2)
K2:K18K2=IF(ISERROR(OFFSET(K2,-1,0,1)+F2-E20),F2-E2,OFFSET(K2,-1,0,1)+F2-E2)
L2:L18L2=RIGHT(C2,LEN(C2)-SEARCH("-",C2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:D18Expression=AND(NOT(ISBLANK(C2));ISERROR(MATCH(C2;categories;0)))textYES
C2:D18Expression=OR(C2="[Balance]";C2="[Transfer]";ISBLANK(C2))textYES
C2:D18Expression=OR(ISERROR(MATCH(C2;yearlyA;0));ISERROR(MATCH(C2;monthlyA;0)))textYES
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

4hoteliers

New Member
Joined
Jan 18, 2016
Messages
42
Balance.xlsx
ABCDEFGHIJKLM
1AccountPayeeCategoryreconcile or clearPaymentDepositCleared PaymentCleared DepositAccount BalanceCleared BalanceBalanceBANK NAMEEACH BANK balance
2Balance EUROBANKBalance - EUROBANK[Balance - EUROBANKc1500001500015,000.0015,000.0015,000.00 EUROBANK
3Balance ALPHA BAKBalance - ALPHA BANK[Balance - ALPHA BANKc8500085008,500.008,500.0023,500.00 ALPHA BANK
4CashInterestINTEREST - EUROBANKc0.1200.120.120.1223,500.12 EUROBANK
5CashInteest TaxINTEREST TAX - EUROBANKc0.020.0200.100.1023,500.10 EUROBANK
6Web BankingVodafonePhone VODAFONE - EUROBANKc21.6121.610(21.61)(21.61)23,478.49 EUROBANK
7Web BankingShared Utility Monthly BillShared Utility Monthly Bill - ALPHAc39.3339.330(60.94)(60.94)23,439.16 ALPHA
8Web BankingOffice funriture BuyOffice Furniture Buy - EUROBANKc73.7573.750(134.69)(134.69)23,365.41 EUROBANK
9Web BankingSoftware BuySoftware Buy - ALPHAc104.13104.130(238.82)(238.82)23,261.28 ALPHA
10Web BankingCar LeasingCar Leasing - EUROBANKc447.64447.640(686.46)(686.46)22,813.64 EUROBANK
11Client DepositClient 1 DepositTHIRA'S DOLPHIN - EUROBANKc1000010001,000.001,000.0023,813.64 EUROBANK
12CashWithraw cashWithdraw - Eurobankc4004000(399.90)(399.90)23,413.64 Eurobank
13CashWithraw cashWithdraw - ALPHAc2002000(599.90)(599.90)23,213.64 ALPHA
14Web BankingAcount PaymentACCOUNTANT PAYMENT - EUROBANKc286.44286.440(972.90)(972.90)22,927.20 EUROBANK
15Web BankingAnnual AdvertisitngAnnual Advertising Hotelier Academy - EUROBANKc330033000(4,272.90)(4,272.90)19,627.20 EUROBANK
16CashWithraw cashWithdraw - ALPHAc2002000(799.90)(799.90)19,427.20 ALPHA
17Web BankingProfessional chamber paymentProfessional Chamber - EUROBANKc25250(4,297.90)(4,297.90)19,402.20 EUROBANK
18Web BankingElectricity bill paymentElectricity - EUROBANKc420.5420.50(4,718.40)(4,718.40)18,981.70 EUROBANK
Sheet1
Cell Formulas
RangeFormula
G2:G18G2=IF(OR(D2="c",D2="R"),E2,0)
H2:H18H2=IF(OR(D2="c",D2="R"),F2,0)
I2:I18I2=SUMIF(A$1:A2,"="&A2,F$1:F2)-SUMIF(A$1:A2,"="&A2,E$1:E2)
J2:J18J2=SUMIF(A$1:A2,"="&A2,H$1:H2)-SUMIF(A$1:A2,"="&A2,G$1:G2)
K2:K18K2=IF(ISERROR(OFFSET(K2,-1,0,1)+F2-E20),F2-E2,OFFSET(K2,-1,0,1)+F2-E2)
L2:L18L2=RIGHT(C2,LEN(C2)-SEARCH("-",C2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:D18Expression=AND(NOT(ISBLANK(C2));ISERROR(MATCH(C2;categories;0)))textYES
C2:D18Expression=OR(C2="[Balance]";C2="[Transfer]";ISBLANK(C2))textYES
C2:D18Expression=OR(ISERROR(MATCH(C2;yearlyA;0));ISERROR(MATCH(C2;monthlyA;0)))textYES
Need to have in column M, the balance of each bank
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,219
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Need balance of each bank

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread as the other one has replies so please continue in the linked thread.
 
Status
Not open for further replies.

Forum statistics

Threads
1,144,582
Messages
5,725,110
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top