Solved

# Offset sum

Posted on 2014-12-02
118 Views
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
Question by:Seamus2626
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +2

LVL 51

Accepted Solution

Rgonzo1971 earned 167 total points
ID: 40475400
Could you send a dummy?
0

LVL 5

Assisted Solution

Hakan YÄ±lmaz earned 167 total points
ID: 40475403
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

LVL 18

Expert Comment

ID: 40475410
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

Author Comment

ID: 40475417
Attached
EE.xlsx
0

LVL 5

Expert Comment

ID: 40475440
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

Author Comment

ID: 40475442
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

LVL 18

Assisted Solution

Simon earned 83 total points
ID: 40475445
EE-SumToPrevMonth.xlsx
0

LVL 5

Assisted Solution

Hakan YÄ±lmaz earned 167 total points
ID: 40475453
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

LVL 51

Assisted Solution

Rgonzo1971 earned 167 total points
ID: 40475482
HI,

pls try

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

Regards
0

Author Comment

ID: 40475533
Have look at the attached guys, doesn't seem to work for me - K16 is not calculating
Copy-of-EE-SumToPrevMonth.xlsx
0

LVL 5

Expert Comment

ID: 40475570
Corrected months column. This column should have actual date values and be formatted same as reporting month.
Copy-of-EE-SumToPrevMonth-rev.xlsx
0

LVL 33

Assisted Solution

Rob Henson earned 83 total points
ID: 40475782
Alternative to get last day of previous month:

=EOMONTH(TODAY(),-1)

Thanks
Rob H
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month6 days, 18 hours left to enroll