Sumproduct or ????

[Jack Deuce]

I need to calculate 'unreconciled amount' in my check register by
looking at column B (deposit or withdrawal), column E (check amount),
column F (bank balance) and column G (reconciled). Column G will
contain either "R" for reconciled or it will be null. All
Uncreconciled (no "R") transactions must be examined to see if they
are deposits or withdrawals (column B), subtracting withdrawals from
deposits.

Can anyone help with a formula?

I currently have a helper column that is used to total the
unreconciled amounts.
(=IF($G278<>"R",IF($B278="Withdrawal",$E278*-1,IF($B278="Deposit",$E278,"")),""))

 

[Ken]

Jack

I don't think you gave enough information to give an exact formula,
but, you did give enough to confirm that SUMPRODUCT is the right way
to go. I would try something like

=SUMPRODUCT(--(G2:G11<>"R"),E2:E11*(IF(B2:B11="Withdrawal",-1,1)))

SUMPRODUCT doesn't usually have to be entered as an array function
(shift-control-enter) but this time it does because of the IF
statement array component that is used to make the withdrawals
negative. My sample data had numbers in rows 2 to 11 and it works the
way I believe it is supposed to. You could avoid the array entering
if you had a helper column to make the withdrawals negative and used
that column instead of column E where I have presumed all numbers are
positive; with cash outflows identifed by the label in column B.

Good luck.

Ken

 

[Jack Deuce]

Thanks Ken. Worked perfectly. I didn't realize you could put the IF
logic turning the amount negative in the sumproduct function. I'll
keep that in mind in the future.