+ Reply to Thread
Results 1 to 3 of 3

difference between SUMPRODUCT versus SUMIFS

  1. #1
    Registered User
    Join Date
    06-18-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question difference between SUMPRODUCT versus SUMIFS

    sum by multiple conditions, search,...
    advantages and disadvantages of each (Sumproduct and Sumifs) for my homework.

    thanks

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: difference between SUMPRODUCT versus SUMIFS

    Try testing the relative speeds of each on a large dataset.......

    .....and please don't post the same question in multiple forums here - see rule 5
    Last edited by daddylonglegs; 06-05-2013 at 03:45 PM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-18-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: difference between SUMPRODUCT versus SUMIFS

    In an exercise to examine the speed of both and with many conditions difference notice the speed in show. But still did not have all that they asked me in my homework.

    On the internet able to find in several places that spoke of each and found these, I welcome your comments on them:

    SUMPRODUCT using arrays to work this makes you used more memory. The more large the operation more slowly will the result.
    Using arrays provides a more general solution.
    SUMPRODUCT can not insert rows and columns.
    SUMPRODUCT can be used to count or add data to several conditions.
    SUMIFS sum cells considering multiple criteria, does not use matrices to be more rapid. Generates cumulative results restricted to sum criteria function as an AND.

    When we want to use an OR condition that is we can not use SUMPRODUCT SUMIFS and should use that to work or operate with ranges or arrays. With SUMPRODUCT can use both conditions (logical AND-OR) that you can use
    * For AND
    + for OR
    Example
    = SUMPRODUCT (A3: A10 * ((B2: B10 = "A")+ (B2: B10 = "c")) * (TYPE = "BIG"))

    ((B2: B10 = "STORE") +(ZONE = "A")) * (TYPE = "BIG")) what it does is evaluate with an OR-AND showing (1 0) where (TYPE = "BIG") assessment to meet the condition cells and shown as 1 or true.
    ((B2: B10 = "A ") +(B2: B10 = "c")) evaluates two conditions and both must be true to return 1 or true.
    Attached Images Attached Images
    Last edited by pl123zorro; 06-07-2013 at 12:43 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1