# difference between SUMPRODUCT versus SUMIFS

1. ## difference between SUMPRODUCT versus SUMIFS

sum by multiple conditions, search,...

thanks

2. ## 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

3. ## 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.

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