# help needed with formula

#### zakizelani

##### New Member
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

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
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
And split those SUMIFs into 2 different columns to make audting easier. No prizes given for the most complicated formula.

Replies
0
Views
58
Replies
1
Views
143
Replies
1
Views
31
Replies
9
Views
296
Replies
0
Views
108

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