Results 1 to 15 of 15
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    SUMIF? - Sum numbers if a condition is met

    My worksheet has a column containing the amounts of checks received for deposit in the bank, and a column containing the dates the checks were deposited. If a check has been deposited, its amount is in the amounts column and there is a date in the corresponding dates column. If a check has been received but not yet deposited, its amount is in the amounts column, but its dates column is blank.

    My objective is to calculate the sum of the amounts for checks that have actually been deposited.

    Range F10:F99, named ChecksReceived, contains blanks or the amounts of the checks received. Range I10:I99, named DatesOfDeposts, contains blanks or the dates of the deposits.

    I've tried various forms of the SUMIF function, but nothing has worked. Descriptions of SUMIF are pretty sketch, IMHO, but I'm thinking that SUMIF isn't the solution to my need.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    PHP Code:
    =SUMPRODUCT(ChecksReceived*(DatesOfDeposits<>"")) 
    will accomplish what you want.

    You could improve this by testing for an actual date in
    DatesOfDeposits rather than simply for a blank cell.
    Last edited by MartinM; 2012-06-16 at 15:40.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    Lou Sander (2012-06-16)

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    I have no idea why the line break in my post is appearing after the first "(". I tried editing my post, only find that more unwanted line breaks mysteriously appeared, so I gave up :-(

    Anyway, I hope it makes sense "as is".

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    To prevent weird spacing include your formula with in [code] formula here [/code] tags, it also makes them easier to copy.
    Code:
    =SUMPRODUCT(ChecksReceived*(DatesOfDeposits<>""))

    BTW you can also use noparse tags which follow the same convention, I actually used these to get the code tags to show above.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    I tried CODE tags and still got unwanted line breaks !

    PHP tags worked though.

    Strange.

  7. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I saw no unwanted line breaks, earlier or now.

    Anyway, the code provided works perfectly. The SUMPRODUCT function has been the key to my last two Excel mysteries. I guess I need to read up on it!

    I guess it works if the expression is TRUE, which equates to 1. If the expression is FALSE, I guess it equates to zero.

    Many, many years ago when I was a BASIC programmer, TRUE equated to -1
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    "
    I guess it works if the expression is TRUE, which equates to 1. If the expression is FALSE, I guess it equates to zero."

    Quite so - in this instance SUMPRODUCT multiplies the amount by the value of the truth test: true (x 1) adds the amount into the total, false (x 0) adds nothing.

    Its a very powerful function, allowing up to 30 tests to be applied.

  9. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just as an additional to this already solved problem.
    You could use SUMIF like this

    Code:
    =SUMIF(DatesOfDeposits,"<>",ChecksReceived)
    This gives the same result, unless I have misread the question.

    SUMPRODUCT is a wonderful function, especially with 2 conditions or more in older versions of Excel.
    But SUMIF has been optimised for the calculation whereas (and I may well be corrected on this),
    SUMPRODUCT is a volatile function.
    This means that it recalculates even if none of the cells effecting it are changed.

    On a bigger spreadsheet, this can have an impact on performance.

    Feel free to shoot me down in flames on that one.
    Andrew

  10. #9
    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
    SUMPRODUCT is not volatile.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    . . . and, curiously, SUMIF can be volatile !

    http://www.decisionmodels.com/calcsecretsi.htm

    One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
    =SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile. Both of these formulae will reference cells B1:B4.
    Presumably this volatile behaviour was added to give correct results when B2:B4 were changed.

  12. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    For us programming plebes on the lower rungs of the excel formulas/programming food chain an example or sample worksheet would prove to be very instructive!!

    Regards,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  13. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    What is it that you'd like an example of ? Sorry if I am being stupid

  14. #13
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hey Martin,

    an excel sheet with data and the subject formula or code showing the results, makes for an easy reference when searching for a solution...thanks for your response...

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  15. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts

    SUMPRODUCT example

    Marty,

    A sample file showing a simple use of SUMPRODUCT is attached.

    Enter your dates in the two pale yellow cells and the cell to the right will show the sum of the amounts that fall between (or on) those dates.

    You can add more conditions inside the SUMPRODUCT function, up to a total of 30.

    The worksheet is protected to prevent you overtyping the formulae but there's no password set.

    Hope this helps.

    NB If you set the Start Date to be after the Finish Date, you will get the result you deserve
    Attached Files Attached Files

  16. The Following User Says Thank You to MartinM For This Useful Post:

    mojave1 (2012-08-12)

  17. #15
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Martin,

    a little slow on the thanks...working toooo much, but thank you!!

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

Posting Permissions

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