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
Microsoft Excel
Last Comment
Rob Henson
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
=SUMIF(A:A,"<" & TODAY()-DAY(NOW()),B:B)