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)