"Shawn O'Donnell" wrote in message
...

Hi Shawn,

I meant COUNTIF(B1,B2), since that's the easiest way to show how COUNTIF
treats empty cells differently, depending on whether the emtpy cell is in
the
'array' or 'criteria' argument. It's instructive to look at the four
possibilities for B1 & B2, then compare the results with the same
arguments
but using COUNTIF(B1,B2&"").

Well as I said, with COUNTIF(B1,B2) I didn't get the point you were making,
but with COUNTIF(B1:B2,B2) I thought I did.

It is not SUM that is the problem here but COUNTIF. SUM can work
on arrays. SUMPRODUCT can work on arrays. COUNTIF expects
to work on a range array, but a criteria value. To work on an array
where a single value, be that a hard-coded value or a celle reference,
you have to use an array formula.

I don't follow you there. How is it that SUMPRODUCT gets to be an array
formula regardless of how you enter it, but SUM has to be entered with
Ctl-Shift-Enter to become an array formula?

What I mean is that SUM will work on an array anyway, COUNTIF will not work
on an array criteria (directly that is). So it SUM doesn;y need to be array
entered nor does SUMPRODUCT, but if you want the COUNTIF criteria to be a
range, the whole thing gets array entered.

All that differs in the two versions is the context provided by SUM &
SUMPRODUCT. Both functions accept arrays, but SUM doesn't seem to provide
an
array context for evaluating functions in its arguments, while SUMPRODUCT
does.

No, again you are looking at the wrongt function.

Here's another example. Say you wanted to know the total number of
letters
in A1:A10.

=SUM(LEN(A1:A10)) will work only if entered as an array formula, but
=SUMPRODUCT(LEN(A1:A10)) will work either way.

If, in the SUM example, you select LEN(A1:A10) and hit F9 to evaluate the
LEN() function, you create an array that SUM can digest, even in a
non-array
formula. But SUM by itself isn't able to interpret the LEN() as an array
without the whole formula being entered as an array formula. SUMPRODUCT
can.

Yes but try =SUM(IF(LEFT(A1:A10,1)="a",LEN(A1:A10))). That doesn't work
unless you array enter it. Again, it is not SUM that causes this but IF.

=SUMPRODUCT(--(LEFT(A1:A10,1)="a"),LEN(A1:A10)) works non-array entered.

Do you know of any documentation concerning this array-producing context
for
evaluating scalar functions on ranges of cells (without having to enter
the
formula as an array formula)?

Bob Umlas wrote the best paper that I have read. You can get it at
http://www.emailoffice.com/excel/arrays-bobumlas.html

Is SUMPRODUCT the only function that provides this context?

Depends upon what you mean. SUMPRODUCT is not the only function that can
work upon ranges/arrays, but it is the only non-array function capable of
multiple condition tests. Read more about it at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html