# Thread: sumproduct vs sumif formula

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

Thanks and regards.

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

3. Hi

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

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
•