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]

I'm sorry Biff ... I've confused you again haven't I?.

It would appear so!

 

[Akashwani]

Hi Biff,

I'm so sorry for wasting your time and for causing you any mental frustration with me. Look at it from the positive side Biff, you have spent 2 days banging your head on this project

Thanks again Biff for your contributions, appreciated as always.

Ak

 

[T. Valko]

Hi Biff,

I'm so sorry for wasting your time and for causing you any mental frustration with me. Look at it from the positive side Biff, you have spent 2 days banging your head on this project

Thanks again Biff for your contributions, appreciated as always.

Ak

Nah, I'm not wasting my time!

Here's what confused me.

You posted this formula:

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

In that context the plus operator (+) means OR.

So, I interpreted it literally that you meant:

Count if the year in column D OR the year in column E is <=A2.

 

[Akashwani]

Ah, you see Biff, that's the obvious difference between us, you know what your doing and what Excel means and I'm just making a complete hash of Excel and an *ss of myself

Thanks

Ak

 

[T. Valko]

Ah, you see Biff, that's the obvious difference between us, you know what your doing and what Excel means and I'm just making a complete hash of Excel and an *ss of myself

Thanks

Ak

Look at it from my perspective...

Every minute you spend in forums like this you're learning and improving your skills!