MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Aladin - thread 21939


Posted by Chris D on February 12, 2002 12:08 PM

Hi Aladin,

In thread 21927a.html the last option using SUMPRODUCT, the last bit has *(C1:C10)

From lots of your previous posts, I have been using ,(C1:C10) very succesfully and making my working life so much easier :-)

I just tested both on the ranges in that example and they both give the same answer, but what's the difference, or does it not matter ?

thanks mate
Chris


Posted by Aladin Akyurek on February 12, 2002 12:34 PM

Chris --

> I just tested both on the ranges in that example and they both give the same answer, but what's the difference, or does it not matter ?

It won't matter here at all. If one would want to be kosher about it, then

=SUMPRODUCT((A1:A10=A30)+0,(B1:B10=B30)+0,C1:C10)

would accord well with the syntax def that the Help spells out.

A bit off topic: I read Harlan Grove saying

QUOTE
"David J. Braden" <dbraden@nospam.nokidding.com> wrote...
>I don't see the difference between your two formulas. Did you mean
>=SUM((A1:A2="a")*B1:B2) for the second?

Yes. Oops!

>Sorry to have assumed equivalent behavior. I very rarely use
>SUMPRODUCT (read: only on someone else's legacy code); always thought
>it was redundant, so I stick to the old habit of array-entering stuff
>with SUM.

Being more pessimistic than you, I assume more internal goings on with array
formulas, so avoid array-entered SUM's in favor of SUMPRODUCT whenever
possible.
UNQUOTE

Harlan is someone who often reflects on the innards of Excel.

=========

Posted by Chris D on February 12, 2002 12:50 PM

s'cool

I've been utilising your =sumproduct((x:xn=a)*(y:yn=b)*(z:zn=c),(t:tn)) variations instead of multi sumifs and they all work fab

Have a good evening
Chris
:-)