help needed with formula

[zakizelani]

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

 

[Richynero]

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.

 

[Richynero]

And split those SUMIFs into 2 different columns to make audting easier. No prizes given for the most complicated formula.