SUMPRODUCT problems with dates

[mas_lerdanch]

=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

 

[Brian from Maui]

Do you want to sum if the dates fall with say October 1st 2002 to September 30 2003?

 

[Yogi Anand]

Hi Mas Lerndach:

So, today is 8-Jul-2007. Please clarify ...

1) from what date (dd-mmm-yyyy) to Today's date you want the data?
2) what column is the data in that you want to sum up?
3) what is the name of the worksheet with data?
4) what is the name of the workbook?

so we can understand what you are working with and what you are trying to accomplish.

 

[mas_lerdanch]

The Data on each sheet runs from Oct 1st - the sum i want is to equal all profit before todays date for that year so i can compare it with this years current figures - so for instance the result for today would be SUM = Total Profit up to 08/07 for any dates prior toy 08/07 including the dates before 1st Jan. the following formula looks for Profit prior to todays date but only gives a result for the profit between the 1st of january and todays date (day and month - not year) so it cant really be compared year on year - below is an example.

formula
=SUMPRODUCT(--(DATE(0,MONTH(Example.xls!Date2003),DAY(Example.xls!Date2003))<=DATE(0,MONTH(TODAY()),DAY(TODAY()))),(Example.xls!Profit2003))

Date
09/10
04/11
28/11
02/12
03/12
02/01
30/01
28/02
28/02
01/03
31/03
01/04
01/04
01/05
01/05
01/05
30/05
30/05
30/05
02/06
03/06
03/06
03/06
03/06
27/06
27/06
27/06
27/06
27/06
27/06
27/06
30/06
30/06
30/06
01/07
15/07
15/07
30/07
31/07
31/07
04/08
04/08
04/09

Campaign
BMC Summit
Internet
Country Walking
Trail
Other
Other
TGO
Recommendation
Trail
Recommendation
Trail Brochure Panels
Internet
Other
Internet
Recommendation
Recommendation
BMC Summit
Trail
Trail
Country Walking
BMC Summit
BMC Summit
BMC Summit
Internet
Other
Recommendation
Saturday Guardian
Saturday Guardian
TGO
Trail Brochure Panels
Trail Brochure Panels
Recommendation
Recommendation
Trail Brochure Panels
Recommendation
Internet
Internet
Recommendation
Internet
Internet
Internet
Recommendation
Other

Profit
£1,080.00
£1,125.00
£1,091.43
£219.43
£251.43
£274.29
£0.00
£357.28
£1,080.00
£1,414.28
£0.00
£466.40
£220.00
£1,001.00
£106.29
£174.86
£450.00
£199.89
£0.00
£246.86
£117.86
£100.29
£0.00
£384.00
£133.71
£668.57
£1,440.00
£0.00
£280.80
£402.86
£43.14
£478.80
£468.00
£466.29
£684.00
£178.28
£0.00
£0.00
£265.71
£0.00
£536.40
£0.00
£204.00

 

[mas_lerdanch]

1) from what date (dd-mmm-yyyy) to Today's date you want the data?

Each sheet is a different year 2003, 2004, 2005, 2006, 2007
29/09 would be the start date up to todays date (08/07) in 2003, 2004 etc

2) what column is the data in that you want to sum up?

4

3) what is the name of the worksheet with data?

Sheet1

4) what is the name of the workbook?

Example

Regards, Mas

 

[Brian from Maui]

If you want A sum of all data before today,

=SUMIF(A:A,"<"&TODAY(),C:C)

 

[Yogi Anand]

Hi Mas:

Thanks for posting the requested information. Following is a solution from a fresh look at what you are trying to accomplish ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B	C	D	E
1	Example
2
3	Date	Campaign	Profit		SumToDate
4	09-Oct-06	BMC Summit	1080.00		4725.58
5	04-Nov-06	Internet	1125.00
6	28-Nov-06	Country Walking	1091.43
7	02-Dec-06	Trail	219.43
8	03-Dec-06	Other	251.43
9	02-Jan-07		274.29
10	-------	-------	-------
11	-------	-------	-------
12	-------	-------	-------
13	-------	-------	-------
14	01-Apr-07
15	30-May-07
16	01-Jul-07	Recommendation	684.00
17	15-Jul-07	Internet	178.28
Sheet3

</body></html>
I am proposing using the Month and Year in the Date comparison leading to a much more straight forward solution.

If you have a problem with this approach, please post back and let us see what else we can do.

 

[mas_lerdanch]

What I am after is a Sum of everything in the profit colum for all the dates before the same day as today in 2003 if I apply that formula it sums the total what i wasa trying to achieve was a comparison for 2003 for the profit taken before the same date as today but in 2003.

http://www.colletts.co.uk/Example.htm

 

[Yogi Anand]

What I am after is a Sum of everything in the profit colum for all the dates before the same day as today in 2003 if I apply that formula it sums the total what i wasa trying to achieve was a comparison for 2003 for the profit taken before the same date as today but in 2003.

http://www.colletts.co.uk/Example.htm

Hi Mas:

Here we go ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B	C	D	E	F
1	Example	2002 - 2003			TODAY()	7/8/2007
2
3	Date	Campaign	Profit
4	09-Oct-02	BMC Summit	1,080.00		Formula to Date	11659.47
5	04-Nov-02	Internet	1,125.00		(in 2003)
6	28-Nov-02	Country Walking	1,091.43
7	02-Dec-02	Trail	219.43
8	03-Dec-02	Other	251.43
9	02-Jan-03	Other	274.29
10	30-Jan-03	TGO	0.00
11	28-Feb-03	Recommendation	357.28
12	28-Feb-03	Trail	1,080.00
13	01-Mar-03	Recommendation	1,414.28
14	31-Mar-03	Trail Brochure Panels	0.00
15	01-Apr-03	Internet	466.40
16	01-Apr-03	Other	220.00
17	01-May-03	Internet	1,001.00
18	01-May-03	Recommendation	106.29
19	01-May-03	Recommendation	174.86
20	30-May-03	BMC Summit	450.00
21	30-May-03	Trail	199.89
22	30-May-03	Trail	0.00
23	02-Jun-03	Country Walking	246.86
24	03-Jun-03	BMC Summit	117.86
25	03-Jun-03	BMC Summit	100.29
26	03-Jun-03	BMC Summit	0.00
27	03-Jun-03	Internet	384.00
28	27-Jun-03	Other	133.71
29	27-Jun-03	Recommendation	668.57
30	27-Jun-03	Saturday Guardian	1,440.00
31	27-Jun-03	Saturday Guardian	0.00
32	27-Jun-03	TGO	280.80
33	27-Jun-03	Trail Brochure Panels	402.86
34	27-Jun-03	Trail Brochure Panels	43.14
35	30-Jun-03	Recommendation	478.80
36	30-Jun-03	Recommendation	468.00
37	30-Jun-03	Trail Brochure Panels	466.29
38	01-Jul-03	Recommendation	684.00
39	09-Jul-03	Internet	178.28
40	15-Jul-03	Internet	0.00
41	30-Jul-03	Recommendation	0.00
42	31-Jul-03	Internet	265.71
43	31-Jul-03	Internet	0.00
44	04-Aug-03	Internet	536.40
45	04-Aug-03	Recommendation	0.00
46	04-Sep-03	Other	204.00
Sheet4

</body></html>
Boy ... It took us so many iterations to finally get to the bottom of your requirement in specific unambiguous terms.

 

[mas_lerdanch]

Dear Yogi,

Thanks a million - I couldn't see the wood for the trees! should be a dynamic workbook when I have finished messing around with it.

All the best,

Mas