when to use array formulas

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

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?
 
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)
 
That's very helpful, thanks.

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)







- Show quoted text -
Click to expand...
 
Back
Top