Sumproduct to countif

[Akashwani]

Hi,

I want to count how many years (From date To date) include a particular year and return all relevant data.

Excel Workbook
	A	B	C	D	E
1	Name	Transport	Colour	From	To
2	Bill	Bus	Red	02/02/2000	20/03/2004
3	Tom	Car	Green	02/02/2003	20/03/2004
4	Jo	Plane	Blue	02/02/2004	20/03/2006
5	Tim	Boat	Yellow	02/02/1999	20/03/2001
6	Sue	Train	Red	02/02/2002	20/03/2002
7	Dan	Bike	Yellow	02/02/2000	20/03/2001
8	Bob	Car	Blue	02/02/2008	20/03/2009
9	Mo	Plane	Green	02/02/2000	20/03/2002
10	Flo	Bus	Red	02/02/2002	20/03/2004
11	Joe	Plane	Green	02/02/1999	20/03/2000
12	Jim	Boat	Blue	02/02/2001	20/03/2002
13	Pat	Train	Red	02/02/2002	20/03/2002
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
1	Year	Count	*	*	*	*	*
2	2000	6	4	4	1	1	1
3	*	*	*	*	*	*	*
4	Name	Transport	Colour	From	To	*	*
5	Bill	Bus	Red	02/02/2000	20/03/2004	*	*
6	Tim	Boat	Yellow	02/02/1999	20/03/2001	*	*
7	Dan	Bike	Yellow	02/02/2000	20/03/2001	*	*
8	Mo	Plane	Green	02/02/2000	20/03/2002	*	*
9	Joe	Plane	Green	02/02/1999	20/03/2000	*	*
10	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	*	*
Sheet2

I know that I am doing something wrong with the Sumproduct formulas, but for the life of me I just cannot see it. The above Sumproduct needs to return 5, not 6 or 4 or 1

Can someone ease my mind?

Thanks

Ak

 

[T. Valko]

Hi,

I want to count how many years (From date To date) include a particular year and return all relevant data.

Excel Workbook
	A	B	C	D	E
1	Name	Transport	Colour	From	To
2	Bill	Bus	Red	02/02/2000	20/03/2004
3	Tom	Car	Green	02/02/2003	20/03/2004
4	Jo	Plane	Blue	02/02/2004	20/03/2006
5	Tim	Boat	Yellow	02/02/1999	20/03/2001
6	Sue	Train	Red	02/02/2002	20/03/2002
7	Dan	Bike	Yellow	02/02/2000	20/03/2001
8	Bob	Car	Blue	02/02/2008	20/03/2009
9	Mo	Plane	Green	02/02/2000	20/03/2002
10	Flo	Bus	Red	02/02/2002	20/03/2004
11	Joe	Plane	Green	02/02/1999	20/03/2000
12	Jim	Boat	Blue	02/02/2001	20/03/2002
13	Pat	Train	Red	02/02/2002	20/03/2002
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
1	Year	Count	*	*	*	*	*
2	2000	6	4	4	1	1	1
3	*	*	*	*	*	*	*
4	Name	Transport	Colour	From	To	*	*
5	Bill	Bus	Red	02/02/2000	20/03/2004	*	*
6	Tim	Boat	Yellow	02/02/1999	20/03/2001	*	*
7	Dan	Bike	Yellow	02/02/2000	20/03/2001	*	*
8	Mo	Plane	Green	02/02/2000	20/03/2002	*	*
9	Joe	Plane	Green	02/02/1999	20/03/2000	*	*
10	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	*	*
Sheet2

I know that I am doing something wrong with the Sumproduct formulas, but for the life of me I just cannot see it. The above Sumproduct needs to return 5, not 6 or 4 or 1

Can someone ease my mind?

Thanks

Ak

One way...

=SUMPRODUCT(SIGN((YEAR(Sheet1!$D$2:$D$13)<=A2)+(YEAR(Sheet1!$E$2:$E$13)<=A2)))

 

[Krishnakumar]

Hi,

=SUMPRODUCT(--(YEAR(D2:D13)<=H1),--(YEAR(E2:E13)>=H1))

where H1 houses the year

HTH

 

[Marcelo Branco]

Hi,

Maybe

Formula in B2
=SUMPRODUCT(--(YEAR(Sheet1!D2:D13)<=A2),--(YEAR(Sheet1!E2:E13)>=A2))

HTH

M

 

[markmzz]

Akashwani,

Or try this formula (array formula - use Ctrl+Shift+Enter and not only Enter) in B2 of Sheet2:

=SUM(IF(YEAR(Sheet1!$D$2:$D$13)<=$A$2,IF(YEAR(Sheet1!$E$2:$E$13)>=$A$2,1,0),0))

Markmzz

 

[T. Valko]

I think you guys are all missing the point.

The OP wants to count ROWS if the year in EITHER column is <= year 2000.

Book1
	D	E	F
2	2/2/2000	3/20/2004	1
3	2/2/2003	3/20/2004	_
4	2/2/2004	3/20/2006	_
5	2/2/1999	3/20/2001	2
6	2/2/2002	3/20/2002	_
7	2/2/2000	3/20/2001	3
8	2/2/2008	3/20/2009	_
9	2/2/2000	3/20/2002	4
10	2/2/2002	3/20/2004	_
11	2/2/1999	3/20/2000	5
12	2/2/2001	3/20/2002	_
13	2/2/2002	3/20/2002	_
Sheet1

 

[Akashwani]

Hi all,

Thanks for your replies.
I've been playing around with this for some time now, this is what I'm getting...

Excel Workbook
	A	B	C	D	E
1	Name	Transport	Colour	From	To
2	Bill	Bus	Red	02/02/2000	20/03/2004
3	Tom	Car	Green	02/02/1998	20/03/2004
4	Jo	Plane	Blue	02/02/2004	20/03/2006
5	Tim	Boat	Yellow	02/02/1999	20/03/2001
6	Sue	Train	Red	02/02/2002	20/03/2002
7	Dan	Bike	Yellow	02/02/2000	20/03/2001
8	Bob	Car	Blue	02/02/2008	20/03/2009
9	Mo	Plane	Green	02/02/2000	20/03/2002
10	Flo	Bus	Red	02/02/2002	20/03/2004
11	Joe	Plane	Green	02/02/1999	20/03/2000
12	Jim	Boat	Blue	02/02/2001	20/03/2002
13	Pat	Train	Red	02/02/2002	20/03/2002
Sheet1

Excel Workbook
	A	B	C	D	E
1	Year	Biff	Marcelo	Kris	Markmzz
2	2000	6	6	6	6
3	*	*	*	*	*
4	Name	Transport	Colour	From	To
5	Bill	Bus	Red	02/02/2000	20/03/2004
6	Tom	Car	Green	02/02/1998	20/03/2004
7	Tim	Boat	Yellow	02/02/1999	20/03/2001
8	Dan	Bike	Yellow	02/02/2000	20/03/2001
9	Mo	Plane	Green	02/02/2000	20/03/2002
10	Joe	Plane	Green	02/02/1999	20/03/2000
11	*	*	*	*	*
12	*	*	*	*	*
Sheet2

OR....

Excel Workbook
	A	B	C	D	E
1	Name	Transport	Colour	From	To
2	Bill	Bus	Red	02/02/2000	20/03/2004
3	Tom	Car	Green	02/02/1998	20/03/2004
4	Jo	Plane	Blue	02/02/2004	20/03/2006
5	Tim	Boat	Yellow	02/02/1999	20/03/2001
6	Sue	Train	Red	02/02/2002	20/03/2002
7	Dan	Bike	Yellow	02/02/2000	20/03/2001
8	Bob	Car	Blue	02/02/2008	20/03/2009
9	Mo	Plane	Green	02/02/2000	20/03/2002
10	Flo	Bus	Red	02/02/2002	20/03/2004
11	Joe	Plane	Green	02/02/1999	20/03/2000
12	Jim	Boat	Blue	02/02/2001	20/03/2002
13	Pat	Train	Red	02/02/2002	20/03/2002
Sheet1

Excel Workbook
	A	B	C	D	E
1	Year	Biff	Marcelo	Kris	Markmzz
2	2004	11	4	4	4
3	*	*	*	*	*
4	Name	Transport	Colour	From	To
5	Bill	Bus	Red	02/02/2000	20/03/2004
6	Tom	Car	Green	02/02/1998	20/03/2004
7	Jo	Plane	Blue	02/02/2004	20/03/2006
8	Tim	Boat	Yellow	02/02/1999	20/03/2001
Sheet2

As you can see in the first example everything is fine.
In the second example the formula from Biff counts every record that is <= 2004, I'm sorry Biff, that is incorrect it should be every record where the year 2004 falls between or is equal to the 2 dates, I've confused you again haven't I?.

The main problem I have now is the formula in A5 (copied across and down) is producing the wrong result. I know it is this part of the formula....

=IF(ROWS(A$5:A5)>$C$2,"",INDEX(Sheet1!A$2:A$13,SMALL(IF((YEAR(Sheet1!$D$2:$D$13)<=$A$2)+(YEAR(Sheet1!$E$2:$E$13)<=$A$2),ROW(Sheet1!$A$2:$A$13)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5))))

But I just can't figure out the correct structure.

My head hurts, suggestions please.

Ak

 

[Krishnakumar]

Hi,

=IF(ROWS(A$5:A5)<=$C$2,INDEX(Sheet1!A$2:A$13,SMALL(IF(YEAR(Sheet1!$D$2:$D$13)<=$A$2,IF(YEAR(Sheet1!$E$2:$E$13)>=$A$2,ROW(Sheet1!$D$2:$D$13)-ROW(Sheet1!$D$2)+1)),ROWS(A$5:A5))),"")

HTH

 

[markmzz]

Akashwani,

I think that your formula is almost ok, only use * and not + and >= and not <=:

=IF(ROWS(A$5:A5)>$C$2,"",INDEX(Sheet1!A$2:A$13,SMALL(IF((YEAR(Sheet1!$D$2:$D$13)<=$A$2)*(YEAR(Sheet1!$E$2:$E$13)>=$A$2),ROW(Sheet1!$A$2:$A$13)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5))))


Markmzz

 

[Akashwani]

Hi,

PERFECT, thank you very much chaps.
I think that I have spent far too long steering at these problems to be able to see or understand anything.

Thanks to you all for your contributions, much appreciated.

Ak