Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMPRODUCT help (Excel 2003)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMPRODUCT help (Excel 2003)

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

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMPRODUCT help (Excel 2003)

    Thanks Hans!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SUMPRODUCT help (Excel 2003)

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

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •