Seamus2626

asked on

# 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

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Attached

EE.xlsx

EE.xlsx

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

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

ASKER

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

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Have look at the attached guys, doesn't seem to work for me - K16 is not calculating

Copy-of-EE-SumToPrevMonth.xlsx

Copy-of-EE-SumToPrevMonth.xlsx

Corrected months column. This column should have actual date values and be formatted same as reporting month.

Copy-of-EE-SumToPrevMonth-rev.xlsx

Copy-of-EE-SumToPrevMonth-rev.xlsx

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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