help needed with formula

zakizelani

New Member
Joined
Mar 3, 2016
Messages
23
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Richynero

Board Regular
Joined
Jan 16, 2012
Messages
148
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

Board Regular
Joined
Jan 16, 2012
Messages
148
And split those SUMIFs into 2 different columns to make audting easier. No prizes given for the most complicated formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,716
Members
412,614
Latest member
Tim McLaughlin
Top