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
:-)
