help needed with formula

zakizelani

New Member
Joined
Mar 3, 2016
Messages
25
hi, i have this table which consist all the transaction happen in the month(Mytransaction). in another table(Summary), i would like to do a summary based on the transaction table. in one of the cells is to input the latest balance of the account

so the problem is that I want a formula to detect under this account the latest balance is this while another account the balance will be that etc...


Table name: Mytransaction

account (A)date(B)deposit(L)withdrawal(K)account balance (O)source balance(Q)
(dropdown list of account)=SUMIF(A$4:OFFSET(A5,0,0,1,1),"="&A5,L$4:OFFSET(L5,0,0,1,1))-SUMIF(A$4:OFFSET(A5,0,0,1,1),"="&A5,K$4:OFFSET(K5,0,0,1,1))=IF(ISERROR(OFFSET(Q5,-1,0,1,1)+L5-K5),L5-K5,OFFSET(Q5,-1,0,1,1)+L5-K5)

sheet name: Transactions (Checkbook)


Table name: Summary

Account (A)Balance
SUMIF('Transactions (Checkbook)'!A:A,$A8,'Transactions (Checkbook)'!L:L)-SUMIF('Transactions (Checkbook)'!A:A,$A8,'Transactions (Checkbook)'!K:K)



SUMIF('Transactions (Checkbook)'!A:A,$A8,'Transactions (Checkbook)'!L:L)-SUMIF('Transactions (Checkbook)'!A:A,$A8,'Transactions (Checkbook)'!K:K)

don't think my current formula is working. would like to use the table range in my formula instead. Is it I have to use a vlookup function? like maybe detect the latest date of the transaction for the account
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I cant trouble shoot your spreadsheet without seeing the data but I would recommend breaking down your formula into small chunks and seeing if you get the expected results. A8 = A1:A10 will be easier to trouble shoot. You can highlight the formula in the formula bar and press F9 to resolve that specific part of the query or use the formula evaluate tool. Also, it is not Excel best practice to reference all 1m rows. Try and use a named range or define your data as a table.
 
Upvote 0
And split those SUMIFs into 2 different columns to make audting easier. No prizes given for the most complicated formula.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top