Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    sumproduct vs sumif formula

    I inherited a worksheet with a SUMPRODUCT formula. It did not pull in the amounts from the inserted schedule into the proper place on the worksheet.

    I played around with it on a different workbook and it worked. I also tried the SUMIF formula and that worked well also. I am attaching the test workbook with one tab SUMPRODUCT and the other tab SUMIF.

    i always heard that SUMPRODUCT was a skiddish formula. WHY IS THAT? and why is it working on this model worksheet and not on the workbook? I kept the columns and everything the same.

    Anyone with some thoughtful answers?

    Thanks and regards.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    IMHO always use the KISS principle! SumProduct was often used in earlier versions of Excel to provide the functionality that is now available with SumIFs, e.g. multiple tests.

    If SumIF works use it, it's simple and straight forward.

    If you need multiple tests use SumIFs.

    USE SumProduct ONLY if it is the only way to get the job done.


    As to why it works in the model and not the original. There is something different somewhere! It's often hard to find, especially with Fixed References ($) which make things harder to read.

    HTH
    Last edited by RetiredGeek; 2015-10-14 at 06:51.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    In addition to RG's guidance, I would add this:
    SUMPRODUCT accepts arrays; SUMIF/S do not

    SUMIF/S work with AND logic only;
    SUMPRODUCT can use OR logic as well as AND logic

    Examples:
    SUMIFS(sum rangeA, if rangeB=this, AND rangeC=that, AND rangeD=whatever, ...)

    SUMPRODUCT(sum rangeA, if rangeB=this, OR rangeC=that, OR rangeD=whatever, AND rangeE> xxx ...)

    (these are not actual formulas - just giving the method)

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-10-14)

  5. #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
    SUMPRODUCT isn't skittish - it's just more precise than SUMIF(S), which is probably what you mean. For example, to SUMIF it doesn't matter whether 4000 in the criteria range is entered as Text or a true number, whereas to SUMPRODUCT there is a difference. I suspect that's what you ran into since your criteria appear to be numeric, but it's hard to say without the workbook where it doesn't work.

    Also note that SUMIF(S) and COUNTIF(S) don't work with closed workbooks whereas SUMPRODUCT will.

    And a small clarification, SUMIFS will work with arrays for the criteria values but you can't use arrays for the criteria or value ranges. In other words, you can use:
    =SUM(SUMIF(A1:A100,{1,2,3},B1:B100)
    (the additional SUM is because you actually get an array of results from the SUMIF part)
    but you can't use:
    =SUMIF({1,2,3},1,{4,5,6})
    for example, or:
    =SUMIF(YEAR(A1:A100),2015,B1:B100)
    because the result of the YEAR function call is an array, not a range.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following 2 Users Say Thank You to rory For This Useful Post:

    RetiredGeek (2015-10-14),zeddy (2015-10-14)

Posting Permissions

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