Does EOMONTH work with array formule?

[thorpyuk]

Hi All,
I have a formula that works perfectly well, but it's a little sluggish:

={SUM(IF(MONTH('Entries'!$C$2:$C$50000)=MONTH(L$34),IF(YEAR('Entries'!$C$2:$C$50000)=YEAR(L$34),'Entries'!$H$2:$H$50000,0),0))}

I thought i might use EOMONTH to speed up rather than evaluating the date twice... it's essentially looking for invoices in a single month matching the month in cell L34.. but so far it doesn't seem to want to play.

If anyone has any ideas to speed it up another way i'd be grateful!

Thanks

 

[FormR]

Hi, is this any better?

=SUMIFS(Entries!$H$2:$H$50000,Entries!$C$2:$C$50000,">="&EOMONTH(L$34,-1)+1,Entries!$C$2:$C$50000,"<"&EOMONTH(L34,0)+1)

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF('Entries'!$C$2:$C$50000-DAY('Entries'!$C$2:$C$50000)+1=EOMONTH(L$34,-1)+1,'Entries'!$H$2:$H$50000))

Or just...

=SUMIFS('Entries'!$H$2:$H$50000,'Entries'!$C$2:$C$50000,">="&EOMONTH(L$34,-1)+1,'Entries'!$C$2:$C$50000,"<="&EOMONTH(L$34))

 

[thorpyuk]

Thanks both, those SUMIFS seem to be working great... i'll test the speeds later. If only there was a function to evaluate spreadsheet formula speed!
Thanks

 

[FormR]

If only there was a function to evaluate spreadsheet formula speed!

Hi, I often use the workbook that @shg posted in post#13 here:

ISBLANK vs LEN (most efficient)

You can also pay for tools, for example:

Why FastExcel V3 - Decision Models

 

[thorpyuk]

Thanks Rory, i'll give that a whirl!