Using Date names instead of values

Marc_w90

Board Regular
Joined
Jul 11, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
How about
Excel Formula:
=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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top