XL2k7 SUMIFS()

  • Thread starter Thread starter ryanb.
  • Start date Start date
R

ryanb.

It appears to me that the new SUMIFS() formula in XL2K7 replaces the need
for SUMPRODUCT formulas (it seems to be quite faster as well). Is this a
correct assumption, or are there certain situations where SUMPRODUCT will
handle a calculation SUMIFS will not (for summing, I realize SUMPRODUCT also
counts)? Wanted to hear back from the experts before I change over my
SUMPRODUCT formulas to SUMIFS. Everyone that would use these reports has
XL2K7.

TIA,
ryanb.
 
You are correct that is a better alternative than say a simple

=SUMPRODUCT(--(rng1=num1),--(rng2="value2"))

but there are many, many instances where it won't work, anywhere in which a
range is manipulated , such as

=SUMPRODUCT(--(MONTH(rng)=3),--(YEAR(rng)=2008))

As far SUMPRODUCT counting, don't forget there is a new COUNTIFS in XL2007
as well.

If your formulae are of the former type, switching to SUMIFS may be worth
your effort, as long as you won't be sharing with users who have an earlier
version.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob! I had yet to experiment with range manipulation in SUMIFS()
formulas... saved me a ton of time.

ryanb.
 
SUMPRODUCT can do more than just sum products, if you use operators for
example, such as * (multiply), / (divide), - (subtract) + (addition) and ^
(exponentiation), it will do as told. If you have 3 numbers in A1:A3 and you
want to divide them by 3 numbers in B1:B3 and sum the quotients then
=SUMPRODUCT(A1:A3/B1:B3) which is the same as =A1/B1 + A2/B2 +A3/B3. .
=SUMPRODUCT(A1:A3+B1:B3) is the same as =(A1+B1)+(A2+B2)+(A3+B3)
=SUMPRODUCT(A1:A3-B1:B3) is the same as =(A1-B1) + (A2-B2) + (A3-B3).
=SUMPRODUCT(A1:A3^B1:B3) is the same as =A1^B1 + A2^B2 + A3^B3. And it may
do other things. I found no documentation in Excel 2007 help that SUMPRODUCT
does these things. All help says is that SUMPRODUCT sums the products. That
and more!

Tyro
 
All help says is that SUMPRODUCT sums the products.

And MS leaves it to your imagination as to how you get the products! There
are multitudes of ways.

Next to IF, SUMPRODUCT may be the most versatile function there is.
 
Acutally SUMPRODUCT is a misnomer. SUMPRODUCT does as told, eg
=SUMPRODUCT(A1:A2^B1:B2) is same as =(A1^B1)+(A^B2). There are no products
here at all. Products are produced by multiplication. What we're doing here
is exponentiation. Perhaps, in this particular case, the function should be
SUMEXPONENTIATION and of course =SUMPRODUCT(A1:A2/B1:B2) would be
SUMQUOTIENT etc.

Tyro
 
=(A1^B1)+(A^B2). There are no products

Actually there is. The product (which can also be described as the argument)
is the result of (A1^B1)+(A^B2).

It's a single product (argument).

At least, that's how I interpret it.
 
When I went to school, products were produced by multiplication. Division
produced quotients and remainders, Summing produced sums and subtraction
produced differences. Perhaps things have changed.

Tyro
 
If you look at an =sumproduct() formula with one argument, like:
=sumproduct(a1:a2)

you could see it as that one argument multiplied by an identity (a
value/array/matrix).

Much like
=3
is the same as
=3*1
or
=3+0

So I'd consider:
=SUMPRODUCT(A1:A2^B1:B2)
this same kind of =sumproduct() formula--one that has a single argument (an
array in this case) with an implied second identity argument:

=SUMPRODUCT(A1:A2^B1:B2,{1;1})
 
I know what you mean!

The product of 10*10 is 100.

What is the product of 10? Is it 10 or 0?

According to Excel:

=PRODUCT(10) = 10

I'm thinking that under the covers SUMPRODUCT is doing something like this
when there's a single argument:

arg * 1

So there is a product. Of course, I could be wrong!
 
There is nothing intrinsically versatile in SUMPRODUCT. The versatility
arises from the fact that multiplying TRUE by TRUE gives 1, etc. Because of
this we have arrays we can sum, but we don't necessarily need SP, an array
SUM does it just as well in the vast majority of cases.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
So, if you don't want to product them, use an array SUM formula, the
exponentiation comes from within.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You are right. In algebra 10 = (1)*10. Therefore (1)*10 =10

In algebra all numbers are presumed to be muliplied by 1. Fundamental law of
algebra.

Therefore -10^2 = -100. But Excel in its comformity to Lotus 1-2-3 and its
conformity to VisiCalc and its error computes -10^2 as 100. VBA computes
the correct answer as: -100. Because: -10^2= -(1)10^2. Exponentiation is
done before applying the unary minus.
 
Back
Top