Using EOMONTH in an Array Formula

[awhiffin]

Hello,

The folllowing function is not working. Does EOMONTH work in array functions as it is returning a VALUE error?

=SUM(IF(ISNUMBER(Improvements!$I2:$I3000)=TRUE,IF(EOMONTH(Improvements!$I2:$I3000,0)=Pivot!P$2,1,0)))

Cheers,

Andrew

 

[barry houdini]

Hello Andrew, welcome to MrExcel,

No, EOMONTH won't accept a range (or array) of dates, try like this

=SUM(IF(ISNUMBER(Improvements!$I2:$I3000),IF(DATE(YEAR(Improvements!$I2:$I3000),MONTH(Improvements!$I2:$I3000)+1,0) =Pivot!P$2,1,0)))

 

[awhiffin]

That works a treat. Thanks very much,

Andrew