mas_lerdanch
Board Regular
- Joined
- Jun 25, 2007
- Messages
- 64
=SUMPRODUCT(--(DATE(0,MONTH('Summer Comparison Yearly.xls'!Date2003),DAY('Summer Comparison Yearly.xls'!Date2003))<=DATE(0,MONTH(TODAY()),DAY(TODAY()))),('Summer Comparison Yearly.xls'!Profit))
Hello, I have a small problem. In my Excel document I have data for our turnover including a 'Date' column a Campaign column and a profit column. I have seperate worksheets for each year 2003, 2004, 2005... The last sheet is 2007 and contains data to date all the other worksheets contain data for the whole financial year which runs from September 30.
I want to compare the profit to date for each year so that it automatically updates each day I open the document. So for instance... the result would be equal to the SUM of Profit to Today (eg 8/07) in each respective year or sheet so I can compare the year with the bookings and turnover made on a daily basis without having to manually update each sheet every time.
The problem with the formula above (setup for the 2003 sheet) is that it does exactly that - If i run it now, it returns the correct data, ie the SUM of Profit up to today for the year. However it only returns data from 01/Jan/2003 - 08/Jul/2003 and none of the data prior to the 1st of Jan as the formula thinks the data for October, November & December is after July not before.
The Data for the Date column is formatted thus dd/mm
Please help.
Mas Lerdanch
Hello, I have a small problem. In my Excel document I have data for our turnover including a 'Date' column a Campaign column and a profit column. I have seperate worksheets for each year 2003, 2004, 2005... The last sheet is 2007 and contains data to date all the other worksheets contain data for the whole financial year which runs from September 30.
I want to compare the profit to date for each year so that it automatically updates each day I open the document. So for instance... the result would be equal to the SUM of Profit to Today (eg 8/07) in each respective year or sheet so I can compare the year with the bookings and turnover made on a daily basis without having to manually update each sheet every time.
The problem with the formula above (setup for the 2003 sheet) is that it does exactly that - If i run it now, it returns the correct data, ie the SUM of Profit up to today for the year. However it only returns data from 01/Jan/2003 - 08/Jul/2003 and none of the data prior to the 1st of Jan as the formula thinks the data for October, November & December is after July not before.
The Data for the Date column is formatted thus dd/mm
Please help.
Mas Lerdanch
