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?

Thanks
Seamus2626Asked:
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.

Rgonzo1971Commented:
Could you send a dummy?
0

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ılmazTechnical Office MEP EngineerCommented:
You can use SumIf formula like this.
=SumIf(DateRange,"<="&Today(),SumRange)
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
0
SimonCommented:
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)
0
Cloud Class® Course: CompTIA Cloud+

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

Seamus2626Author Commented:
Attached
EE.xlsx
0
Hakan YılmazTechnical Office MEP 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.
EE.xlsx
0
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

Thanks
0
SimonCommented:
Please see attached solution.
EE-SumToPrevMonth.xlsx
0
Hakan YılmazTechnical Office MEP 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.
0
Rgonzo1971Commented:
HI,

pls try

=SUM(OFFSET(B2,0,0,MONTH($B$16),1))

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

=EOMONTH(TODAY(),-1)

Thanks
Rob H
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.