View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Ken is offline
external usenet poster
 
Posts: 207
Default Sumproduct or ????

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