• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 3
  • 2
  • +2
6 Solutions
 
Rgonzo1971Commented:
Could you send a dummy?
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 HensonIT & Database AssistantCommented:
Alternative to get last day of previous month:

=EOMONTH(TODAY(),-1)

Thanks
Rob H
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now