Allow ranges in any Excel formula

Question

In Excel, some functions accept ranges, which can be used with SUMPRODUCT and other array functions:

=SUMPRODUCT(MONTH(A1:A4) ...)       <-- no problem

Other functions do not:

=SUMPRODUCT(EOMONTH(A1:A4, 0) ...)  <-- ERROR

Is there a way to force functions like EOMONTH to work with ranges?

Answer 1

Typically the ex "Analysis ToolPak" functions like EOMONTH, WORKDAY, NETWORKDAYS, WEEKNUM etc. will accept arrays in Excel 2007 or later - so you can use a "co-ercer" like +0 or *1 to change an array to a range, e.g.

=SUMPRODUCT(--(WEEKNUM(A1:A10+0)=1))

That will give you a count of how many of the dates in A1:A10 are in week 1 (as defined by WEEKNUM) - without the +0 it doesn't work.

There are other functions - like VLOOKUP or INDEX which can't easily output arrays - those can't be co-erced in the same way