Sum(If(Year) and (Month) formula help needed

[pbt]

Good morning folks,

I have the following wooksheets;
Sheet 1

Test Formula.xls
	J	K	L	M
2	1/16/2006	$5,878.20
3	1/17/2006	$5,402.20
4	1/8/2007	$5,511.20
5	2/17/2006	$5,983.00
6	2/18/2007	$5,330.85
7	3/17/2006	$5,125.20
8	3/18/2007	$5,775.40
9	4/17/2006	$6,074.75
10	5/17/2006	$6,614.30
11	5/18/2007	$5,878.20
12	6/7/2006	$4,212.20
13	6/8/2007	$5,415.20
Sheet1

Sheet 2

Test Formula.xls
	A	B	C	D
1		CurrentYear		CurrentYear+1
2
3	JAN	$11,280.40		$5,511.20
4	FEB	$5,983.00		$5,330.85
5	MAR	$5,125.20		$5,775.40
Sheet2

The formulas on Sheet to continue for all twelve months.

What I am looking for, in col B, is something to replace =2006 to "current year" and col D, =2007 to"current year +1.

Also the range of 1470 is an abitrary number at this time. I would need the formula to look at the whole column because length of data may be greater than 1470 rows.

I have tried $J2:$J but that does not work. Don't know if this is an Index issue. Not well veresed on Index.

Any help appreciated.

Thanks
h.h.

 

[NBVC]

Try this in B3

=SUM(IF(YEAR(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,J:J)))=YEAR(TODAY()),IF(MONTH(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,J:J)))=1,INDIRECT("Sheet1!$K$2:$K$"&MATCH(9.99999999E+307,K:K)))))

and this in C3

=SUM(IF(YEAR(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,J:J)))=YEAR(TODAY())+1,IF(MONTH(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,J:J)))=1,INDIRECT("Sheet1!$K$2:$K$"&MATCH(9.99999999E+307,K:K)))))

 

[Aladin Akyurek]

B1, copied across:

=YEAR(TODAY())+ROWS($B$1:B1)-1

B3, copied across & down:

=SUMPRODUCT(--(Sheet1!$J$2:$J$1470-DAY(Sheet1!$J$2:$J$1470)+1=("1-"&$A3&"-"&B$1)+0),Sheet1!$K$2:$K$1470)

If you are on Excel 2003, convert the data area on Sheet1 into a List by means of Data|List|Create List. This allow the formulas on Sheet2 to automatically update.

 

[pbt]

My apologies guys, I may have confused the situation by trying to build a sheet that I thought would read better.

Row 1 is not actually there. I just put it there for clarity.

This is the actual sheets with the formulas:
This one for 2006 (will change to Current Year)

SCSP 2006 FORECAST test 7.xls
	A	B	C	D	E
1	JANAURY	LH	ST	RETENTION	TOTAL
2	DELWEBBSUNCITY	11,280.40	10,976.00	3,723.80	25,980.20
3	FUTURE1				0.00
4	FUTURE2				0.00
5	TOTAL				25,980.20
6	FEBRUARY
7	DELWEBBSUNCITY	0.00	0.00	1,823.15	1,823.15
8	FUTURE1				0.00
9	FUTURE2				0.00
10	TOTAL				1,823.15
11	MARCH
12	DELWEBBSUNCITY	5,125.20	5,488.00	0.00	10,613.20
13	FUTURE1				0.00
14	FUTURE2				0.00
15	TOTAL				10,613.20
RECAP 2006

This one for 2007 (will change to Current Year +1)

SCSP 2006 FORECAST test 7.xls
	A	B	C	D	E
1	JANAURY	LH	ST	RETENTION	TOTAL
2	DELWEBBSUNCITY	11,494.20	9,548.00	548.80	21,591.00
3	FUTURE1				0.00
4	FUTURE2				0.00
5	TOTAL				21,591.00
6	FEBRUARY
7	DELWEBBSUNCITY	5,330.85	0.00	0.00	5,330.85
8	FUTURE1				0.00
9	FUTURE2				0.00
10	TOTAL				5,330.85
11	MARCH
12	DELWEBBSUNCITY	5,775.40	11,372.50	0.00	17,147.90
13	FUTURE1				0.00
14	FUTURE2				0.00
15	TOTAL				17,147.90
RECAP 2007

NBVC ~ I got a #N/A with your formula. Tried Enter and Sht/Ctrl Enter

Aladin ~ =YEAR(TODAY())+ROWS($B$1:B1)-1 gave me 2006 in every cell copied across

I don't think that I need that onless I have to "hide" it somewhere out of sight.

I am using Excel 2003, and your other formula works with or without converting data to List. Provided B1 has a date.

Thanks
h.h.

 

[NBVC]

Deleted... See next post.

 

[NBVC]

I see where I went wrong...I didn't reference Sheet 1 in my Match() formulas

Please try:

=SUM(IF(YEAR(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,Sheet1!J:J)))=YEAR(TODAY()),IF(MONTH(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,Sheet1!J:J)))=ROW()-2,INDIRECT("Sheet1!$K$2:$K$"&MATCH(9.99999999E+307,Sheet1!J:J)))))

and

=SUM(IF(YEAR(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,Sheet1!J:J)))=YEAR(TODAY())+1,IF(MONTH(INDIRECT("Sheet1!$J$2:$J$"&MATCH(9.99999999E+307,Sheet1!J:J)))=ROW()-2,INDIRECT("Sheet1!$K$2:$K$"&MATCH(9.99999999E+307,Sheet1!J:J)))))

 

[Aladin Akyurek]

...
Aladin ~ =YEAR(TODAY())+ROWS($B$1:B1)-1 gave me 2006 in every cell copied across
...

My fault. It must be COLUMNS, not ROWS.

 

[pbt]

NBVC ~ Your first formula is summing the whole column. I'm after just summing Month values (Jan, Feb, etc.) for the year.
B3 Jan, B4 Feb, B5 Mar

Second formula returns 0

Aladin ~ Your formulas work, provided I put =YEAR(TODAY())+COLUMNS($B$1:B1)-1 somewhere for it to reference.
(Change PC settings to test for 2007, 2008, & 2009)

Would that have to be my solution for this? And I did not have to change data to List.

Thanks
h.h.

 

[Aladin Akyurek]

NBVC ~ Your first formula is summing the whole column. I'm after just summing Month values (Jan, Feb, etc.) for the year.
B3 Jan, B4 Feb, B5 Mar

Second formula returns 0

Aladin ~ Your formulas work, provided I put =YEAR(TODAY())+COLUMNS($B$1:B1)-1 somewhere for it to reference.
(Change PC settings to test for 2007, 2008, & 2009)

Would that have to be my solution for this? And I did not have to change data to List.

Thanks
h.h.

Harry,

If you have the setup you initially suggested and you make use of the List functionality, no doubt you'll enjoy its benefits.

 

[NBVC]

Based on your original information, my seem to work:

Book3
	J	K	L	M
1
2	1/16/2006	$5,878.20
3	1/17/2006	$5,402.20
4	1/8/2007	$5,511.20
5	2/17/2006	$5,983.00
6	2/18/2007	$5,330.85
7	3/17/2006	$5,125.20
8	3/18/2007	$5,775.40
9	4/17/2006	$6,074.75
10	5/17/2006	$6,614.30
11	5/18/2007	$5,878.20
12	6/7/2006	$4,212.20
13	6/8/2007	$5,415.20
Sheet1

Book3
	A	B	C	D
1
2		2006	2007
3	January	11280.4	5511.2
4	February	5983	5330.85
5	March	5125.2	5775.4
6
Sheet2