"Sandy Mann" wrote...
Harlan Grove" wrote in message
I'm not a purist about separating all terms in SUMPRODUCT, but there's
some value in separating the values summed from the criteria, so

Debatable point. There seems to be a trend for people in the NG's to
recommend comma separation which, I have read, is slighly faster but I
have always been of the opinion that I would rather see an error returned
than a zero which may go unnoticed. . . .

When have I ever shied away from a debatable point?!

Depends on whether one wants a numeric result from

=SUMPRODUCT((Condition1)*...*(ConditionN),RangeToB eSummed)

when

=SUM(RangeToBeSummed)

produces a numeric result. If so, then better to use commas. If not, then it
begs the question whether simple SUM formulas should always be changed to

=SUMPRODUCT(--RangeToBeSummed)

to catch errors all the time. Consistency is a virtue.

"Harlan Grove" wrote in message
...

When have I ever shied away from a debatable point?!

You Harlan - never!!

to catch errors all the time. Consistency is a virtue

So your argument is that because SUM() ignores text, all functions should
ignore text?

Perhaps if we are using it as a SUM() function, but here are we not in fact
using, (or misusing), it as a VLOOKUP() which does return errors at times.
So in that instance is it not more consistent to return errors than to
ignore them?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
...
"Sandy Mann" wrote...
Harlan Grove" wrote in message
I'm not a purist about separating all terms in SUMPRODUCT, but there's
some value in separating the values summed from the criteria, so

Debatable point. There seems to be a trend for people in the NG's to
recommend comma separation which, I have read, is slighly faster but I
have always been of the opinion that I would rather see an error returned
than a zero which may go unnoticed. . . .

When have I ever shied away from a debatable point?!

Depends on whether one wants a numeric result from

=SUMPRODUCT((Condition1)*...*(ConditionN),RangeToB eSummed)

when

=SUM(RangeToBeSummed)

produces a numeric result. If so, then better to use commas. If not, then
it begs the question whether simple SUM formulas should always be changed
to

=SUMPRODUCT(--RangeToBeSummed)

to catch errors all the time. Consistency is a virtue.

"Sandy Mann" wrote in message
...

<snip (this time<g)

I should of course finished with:

Listening out

<g

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote...
....
So your argument is that because SUM() ignores text, all functions should
ignore text?

No. You're oversimplifying.

My argument is that ignoring text in conditional summing is as desirable as
ignoring text in nonconditional summing. That is, if

=SUM(Range)

produces a numeric result (and it always will unless there are error values
in some of the cells in Range), and since

=SUMIF(OtherRange,Criterion,Range)

would then also produce a numeric result NO MATTER WHAT'S IN OtherRange as
long as it's the same size/shape as Range, then consistency would make it
desirable for

=SUMPRODUCT((Condition1)*...*(ConditionN),Range)

also to produce a numeric result.

Perhaps if we are using it as a SUM() function, but here are we not in
fact using, (or misusing), it as a VLOOKUP() which does return errors at
times. So in that instance is it not more consistent to return errors
than to ignore them?
....

Ah, yes, this entire thread was originally about lookups. And if there were
nonnumeric text in some record that DIDN'T match the criteria but a number
in the record that DID match the criteria, what should the formula return?

But if there were text in the field sought for the record matching the
criteria, would it be better to return the text value or #VALUE! ?

But that all ignores the point Jim Cone brought up: SUMPRODUCT fails
miserably when there could be multiple records matching the criteria. For
lookup tasks, use lookup functions or MATCH.

Your use of SUMPRODUCT was ill-considered for lookup tasks, and it's
suboptimal/inconsistent for conditional summing.

"Sandy Mann" wrote...
....
. . . it as a VLOOKUP() which does return errors at times.
....

VLOOKUP returns errors only if (1) there's no lookup value found, or (2)
there's an error value in the return value column in the record matching the
lookup value. Your formula will reproduce the second result, but would
unhappily also return error values if there were any error values anywhere
in any of the ranges you're processing, which is UNLIKE VLOOKUP behavior.
OTOH, if there were no error values in any of the ranges and only numbers or
blank cells in the result range, your formula would return 0 rather than an
error value. For a lookup operation, there should be a clearer indication of
failing to find a match.

One day Harlan.......... One day.......

<g

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk