when to use array formulas
 

The following formula works if it is entered as an array formula, but
it also works if it is entered conventionally:

=SUMPRODUCT(--(U$1:U$382=A390),--(ISBLANK(AL$1:AL$382)))

However, this formula ONLY works if it is entered as an array formula:

=SUM(--ISBLANK(AL2:AL382))

Presumably there is some logic to this distinction.

What am I missing here?

when to use array formulas
 

Basically, you use an array formula when you want a function that normally
returns only a single value to return an array of values. In the example you
cite, SUMPRODUCT works without being entered as an array formula because
SUMPRODUCT is designed to work with arrays already. There is no reason to
force it to do so with CTRL+SHIFT+ENTER. However, the ISBLANK function
normally returns only a single value. Thus, you must use CTRL+SHIFT+ENTER to
tell Excel to return an array of values from ISBLANK. When array entered,
ISBLANK returns an array of results rather than a single result.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Dave F" wrote in message
ups.com...
The following formula works if it is entered as an array formula, but
it also works if it is entered conventionally:

=SUMPRODUCT(--(U$1:U$382=A390),--(ISBLANK(AL$1:AL$382)))

However, this formula ONLY works if it is entered as an array formula:

=SUM(--ISBLANK(AL2:AL382))

Presumably there is some logic to this distinction.

What am I missing here?

when to use array formulas
 

That's very helpful, thanks.

On Jul 5, 9:05 am, "Chip Pearson" wrote:
Basically, you use an array formula when you want a function that normally
returns only a single value to return an array of values. In the example you
cite, SUMPRODUCT works without being entered as an array formula because
SUMPRODUCT is designed to work with arrays already. There is no reason to
force it to do so with CTRL+SHIFT+ENTER. However, the ISBLANK function
normally returns only a single value. Thus, you must use CTRL+SHIFT+ENTER to
tell Excel to return an array of values from ISBLANK. When array entered,
ISBLANK returns an array of results rather than a single result.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"Dave F" wrote in message

ups.com...



The following formula works if it is entered as an array formula, but
it also works if it is entered conventionally:

=SUMPRODUCT(--(U$1:U$382=A390),--(ISBLANK(AL$1:AL$382)))

However, this formula ONLY works if it is entered as an array formula:

=SUM(--ISBLANK(AL2:AL382))

Presumably there is some logic to this distinction.

What am I missing here?- Hide quoted text -

- Show quoted text -