Sum total across months to date.

[Phil Payne]

Hello all,

I have a row of cells showing calendar months across a number of years. Below that on another row are cells indicating costs for each of those months.

I am trying to determine if there is a way (formula or vba) to have the Today() date dictate the range ( from first month to today (this month) top row) and sum the costs (in the other row) of those months to date.

Hopefully my description is not too confusing.

Unfortunately I cannot install xl2bb and I'm reluctant to link to a file on my PC so I've provided an image.

I hope someone can steer me in the right direction!

Thanks

 

[Tetra201]

Maybe

=SUMIFS(5:5,4:4,">=2023-4-1",4:4,"<"&EOMONTH(TODAY(),0)+1)

 

[Phil Payne]

Thankyou Tetra,

I plugged this in and the good news is there were no error messages. so the formula is good.

Unfortunately the range it selects is the whole row from Column A - XlToRight

Great attempt though, thanks for taking the time to help.

Any other thoughts?

 

[MARK858]

Any other thoughts?

Please can you post a sample of your data that we can test on rather than an image (see below)

I'm reluctant to link to a file on my PC so I've provided an image.

You don't link to a file on your PC, if you can't download XL2BB then you upload a sanitized version of your file to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it provides in the thread

 

[Phil Payne]

Thanks Mark,

It will take me a while to provide a sanitised worksheet as I need to sleep - sometime - but I will get it done.

Thanks again.

 

[Leo Skywalker]

Hi there,

You can try:

=SUMIFS(D5:O5,D3:O3,"<="&H10)

 

[Phil Payne]

Thanks Mark,

It will take me a while to provide a sanitised worksheet as I need to sleep - sometime - but I will get it done.

Thanks again.

Created a sanitised version then saw this so not using xl2bb!!!

Thanks for your efforts.

 

[MARK858]

Created a sanitised version then saw this so not using xl2bb!!!

Windows (since Windows 10) gives that warning with all files downloaded from the internet or email that contain macro's.
You need to unblock the file (it does explain it in the link in the XL2BB instructions which takes you to the instructions below)....

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that comes from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file. (Below is the dialog for Peltier Tech Charts for Excel, my commercial Excel charting add-in.)

Check the Unblock box, and click OK.

 

[Phil Payne]

Hi there,

You can try:

=SUMIFS(D5:O5,D3:O3,"<="&H10)

Hello Leo,

Thanks, I thought this was it as logic is good (and simple) but it returns "0".

Trying to calculate using dates is bemusing at times, a good result can depend on the format of the cells.

I've tried using &H20 and &H11 and other formats but no joy..

 

[Phil Payne]

Hold the bus Leo,
This works. The range in the table was 2024/2025 we were looking for 2023 - my fault!
However it is returning the result ignoring April (the first month) and I know why!
I'm going to mark your original post as the solution.
Thank you very much.