Not familiar with SUMIFS in 2007. Are SUMIFS and SUMPRODUCT the same?

If not what is the difference? Thanks

On Dec 7, 6:52*pm, wx4usa wrote:
Not familiar with SUMIFS in 2007. Are SUMIFS and SUMPRODUCT the same?

If not what is the difference? *Thanks

SUMPRODUCT would add the product of 2 rows or columns
sumproduct(a1:a2,b1:b2)

5 4 =20
2 3=6
total 26

SUMIF would add those numbers in a row that met a criteria

1000
2000
2500
3000
sumif(a1:a4,"2000")=5500

No, they are not the same.

With SUMIFS you can't "manipulate" arrays like you can in SUMPRODUCT.

This is a very basic example but it demonstrates the difference.

1/21/2008...X...10
5/27/2008...Y...15

If you wanted to get the sum for the month of January and "X"...

=SUMPRODUCT(--(MONTH(A1:A2)=1),--(B1:B2="X"),C1:C2)

We can "manipulate" the array by testing for the month number using
SUMPRODUCT. You can't "manipulate" an array using SUMIFS.

SUMIFS handles only "constant" comparisons just like SUMIF.

Jan...X...10
May.X...15

=SUMIFS(C1:C2,A1:A2,"Jan",B1:B2,"X")


--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
Not familiar with SUMIFS in 2007. Are SUMIFS and SUMPRODUCT the same?

If not what is the difference? Thanks

No, they are very different in the MS concept.

SUMIFS allows the summation of a range dependent upon multiple conditional
tests on ranges.

SUMPRODUCT multiples tow or more arrays and sums the results.

Of course, SUMPRODUCT has been (ab)used by spreadsheet developers to
incorporate conditional tests (something MS seem largely oblivious to), in a
manner similar to the old familiar SUMIF, but even more similar to the new
Excel 2007 COUNTIFS and SUMIFS. But COUNTIFS and SUMIFS does not supersede
SUMPRODUCT because SUMPRODUCT is so much more versatile than COUNTIFS and
SUMIFS (and thereby than COUNTIF and SUMIF), it allows the inclusion of
extra functions to be acted upon the ranges being tested or counted or
summed. Here is a relatively complex example which calculates how many dates
in a range have a week number of 20

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=20),B$2:B$200)

this is not possible with SUMIF, and so cannot be done with SUMIFS, but as
you see can be done with SUMPRODUCT.

Of course, these modified conditions could be added to separate columns and
COUNTIFS and SUMIFS act upon these columns, which might actually be more
efficient, but that is another question.

--
__________________________________
HTH

Bob

"wx4usa" wrote in message
...
Not familiar with SUMIFS in 2007. Are SUMIFS and SUMPRODUCT the same?

If not what is the difference? Thanks