Using Date names instead of values

[Marc_w90]

Hi,

Hoping someone can tell why this isn't working or offer me an alternative non VBA solution.

The users of this sheet are using date names (Jan, Feb, etc) rather than actual dates (01.01.22 etc)

I'm then trying to use sumifs with a less than function, but from my testing it looks like I needed to convert them all into numbers rather than names.

I'm currently using the below formula but its not working and I'm not sure why. F9 on each individual part works but it doesn't like the formula as a whole.

=SUMIFS(BYM!K:K,BYM!B:B,"",MONTH(DATEVALUE(BYM!AA:AA&1)),">="&MONTH(DATEVALUE($D$22&1)))

Column K = Totals I am summing up
Column B = A status that just needs to be blank
Column AA = Date by name (Jan, feb, etc)
D22 = the date it needs to be less than

Does sumif not like Month(datevalue) on an array or something? or am I just blind to something obvious?

Thanks
Marc

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This part MONTH(DATEVALUE(BYM!AA:AA&1)) returns an array & the xxxIF(S) functions can only use a range.

 

[Marc_w90]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This part MONTH(DATEVALUE(BYM!AA:AA&1)) returns an array & the xxxIF(S) functions can only use a range.

Thanks.

That at least explains why I couldn't get it to work.
But that does lead me to ask, is there an alternative solution?
For temporary purposes, I've put in an extra column in that does the month(datevalue) bit for me and I use that as the criteria range.
However, due to people sometimes adding new lines rather than starting a new line at the bottom where the formula has been copied into, that formula can get missed and I'd rather resolve it all within the Sumif function if possible.

Thanks,
Marc

 

[Fluff]

How about

=SUM(FILTER(BYM!K:K,(BYM!B:B="")*(MONTH(DATEVALUE(BYM!AA:AA&1)))>=MONTH(DATEVALUE($D$22&1))))

but you should limit the number of rows rather than using whole columns