SUMPRODUCT help (Excel 2003)

Topic

New Reply

I have the following formula in cell G9 of the Summary tab.

=SUMPRODUCT(AvailDate<=$C9,Stmt)-SUMPRODUCT(MailDate<=SDate,Stmt)

It doesn't appear to be working and I am not quite sure why. Perhaps this isn't the most efficient way to go about it. I picked this because it was the only one that I knew of that would allow me to use the <=. What I am trying to do is calculate a holdover amount, which is total received less total processed.

Thanks for any assistance that you can provide!

Reply | Quote

Replies

Try

=SUMPRODUCT((AvailDate<=$C9)*Stmt)-SUMPRODUCT((MailDate<=SDate)*Stmt)

Explanation: by using *, you force Excel to interpret the result of AvailDate<=$C9 and of MailDate<=SDate as numbers (true = 1 and false = 0).

Reply | Quote

Thanks Hans!

Reply | Quote

Incidentally, you could just use SUMIF for this:
=SUMIF(AvailDate,"<="&$C9,Stmt)-SUMIF(MailDate,"<="&SDate,Stmt)
FWIW

Reply | Quote