Offset sum

So I have to sum a column with dates Jan-Dec.

But I only sum to the last month, so today I would sum all the numbers in rows Jan-Nov - there is data in all cells Jan-Dec

Currently we manually adjust the formula, is there any way I could automatically sum the correct range by using offset or by instructing of the current month?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Could you send a dummy?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hakan YılmazProject EngineerCommented:
You can use SumIf formula like this.
You can change "Today()" with a fixed cell refence which has the latest date you want to sum.

I don't suggest but you can use whole columns while defining DateRange and SumRange.
Like =SumIf(A:A,"<"&Today(),B:B).

It is better to define a table, and use table columns as reference ranges. It will be a lot faster.
You can look here for information about creating and deleting tables
To sum a column of figures in B with dates in A up to the last day of the previous month...

=SUMIF(A:A,"<" & TODAY()-DAY(NOW()),B:B)
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Seamus2626Author Commented:
Hakan YılmazProject EngineerCommented:
I changed formulas in your sheet in the file attached.

I suggest you to define a table and record values only from top to bottom. And then you can use pivot tables to see reports easily.
Seamus2626Author Commented:
My sum needs to be in the same column, so I am summing k4-k15 in k17

I have a reference cell which gives the correct reporting month - so in k20 I have today()-31 which gives me Nov-14

So =SUMIF(A:A,"<" & TODAY()-DAY(NOW()),B:B)  is turned to =SUMIF(J4:J15,"=" & J20,K4:K15)

Where J20 = Nov-14
J4-J15 is Jan-Dec & K4-K15 are the numbers

Please see attached solution.
Hakan YılmazProject EngineerCommented:
Excel doesn't keeps dates as months in cell values. It treats all dates as "days". So you have exactly defined 1 day in cell J20 which is 31 days ago. If you want the latest day of past month, it is =DATE(YEAR(TODAY());MONTH(TODAY());0). The file i sent was summing in the same column with data.

pls try


Seamus2626Author Commented:
Have look at the attached guys, doesn't seem to work for me - K16 is not calculating
Hakan YılmazProject EngineerCommented:
Corrected months column. This column should have actual date values and be formatted same as reporting month.
Rob HensonFinance AnalystCommented:
Alternative to get last day of previous month:


Rob H
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.