Need balance of each bank

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 for each bank. What kind of formula should I use?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
255
Office Version
  1. 2013
Platform
  1. Windows
Is it what you want?

Book4
BCDEFGHIJKLM
1PayeeCategoryreconcile or clearPaymentDepositCleared PaymentCleared DepositAccount BalanceCleared BalanceBalanceBANK NAMEEACH BANK balance
2Balance - EUROBANK[Balance - EUROBANKc15000015000150001500015000EUROBANK259823.3
3Balance - ALPHA BANK[Balance - ALPHA BANKc8500085008500850023500ALPHA BANK23500
4InterestINTEREST - EUROBANKc0.1200.120.120.1223500.12EUROBANK259823.3
5Inteest TaxINTEREST TAX - EUROBANKc0.020.0200.10.123500.1EUROBANK259823.3
6VodafonePhone VODAFONE - EUROBANKc21.6121.610-21.61-21.6123478.49EUROBANK259823.3
7Shared Utility Monthly BillShared Utility Monthly Bill - ALPHAc39.3339.330-60.94-60.9423439.16ALPHA89341.28
8Office funriture BuyOffice Furniture Buy - EUROBANKc73.7573.750-134.69-134.6923365.41EUROBANK259823.3
9Software BuySoftware Buy - ALPHAc104.13104.130-238.82-238.8223261.28ALPHA89341.28
10Car LeasingCar Leasing - EUROBANKc447.64447.640-686.46-686.4622813.64EUROBANK259823.3
11Client 1 DepositTHIRA'S DOLPHIN - EUROBANKc1000010001000100023813.64EUROBANK259823.3
12Withraw cashWithdraw - Eurobankc4004000-399.9-399.923413.64Eurobank259823.3
13Withraw cashWithdraw - ALPHAc2002000-599.9-599.923213.64ALPHA89341.28
14Acount PaymentACCOUNTANT PAYMENT - EUROBANKc286.44286.440-972.9-972.922927.2EUROBANK259823.3
15Annual AdvertisitngAnnual Advertising Hotelier Academy - EUROBANKc330033000-4272.9-4272.919627.2EUROBANK259823.3
16Withraw cashWithdraw - ALPHAc2002000-799.9-799.919427.2ALPHA89341.28
17Professional chamber paymentProfessional Chamber - EUROBANKc25250-4297.9-4297.919402.2EUROBANK259823.3
18Electricity bill paymentElectricity - EUROBANKc420.5420.50-4718.4-4718.418981.7EUROBANK259823.3
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))
M2:M18M2=SUMIF(L:L,L2,K:K)
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
114
Office Version
  1. 365
Platform
  1. Windows
I think you are looking for the balance by bank at the end of each transaction, in which case put below formula in M2 and drag on
Excel Formula:
=SUMIF(L$1:L2,L2,F$1:F2)-SUMIF(L$1:L2,L2,E$1:E2)

Also, you may want to check the column K formula, there could be a typo in cell reference though it's not skewing the result.
 

4hoteliers

New Member
Joined
Jan 18, 2016
Messages
42
Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?
 

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
255
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?
Is
ALPHA BANK

and

ALPHA

Same?
if it is then you need to fix it.
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
114
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?
Hi 4hoteliers,

As you can see the issue is more with column L not picking up bank names correctly, and ultimately it's column C not displaying bank names consistently.

The easiest way is to directly change C3 from "[Balance - ALPHA BANK" to "[Balance - ALPHA", or L3 to "ALPHA". If that's not feasible you need to specify on how bank names look like in your data pool.
 

4hoteliers

New Member
Joined
Jan 18, 2016
Messages
42
Hadtest, I made a mistake...the bank should be ALPHA (not ALPHA BANK). I will correct it. You think that if I correct the name ALPHA in the cells, your formula will work???
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
114
Office Version
  1. 365
Platform
  1. Windows
Hadtest, I made a mistake...the bank should be ALPHA (not ALPHA BANK). I will correct it. You think that if I correct the name ALPHA in the cells, your formula will work???
I think so, let me know otherwise.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,281
Messages
5,635,295
Members
416,851
Latest member
zeldadav

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