W
wx4usa
Not familiar with SUMIFS in 2007. Are SUMIFS and SUMPRODUCT the same?
If not what is the difference? Thanks
If not what is the difference? Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
rub
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
T
T. Valko
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")
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")
B
Bob Phillips
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.
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.