Count Columns between first populated cell and last populated cell

[dversloot1]

Hello,

I've got sales data by item by year. Items are listed in rows, and their sales by year are in columns. I'm trying to calculate the lifespan of products; Last Year Item was sold - First Year Item was sold. I thought I could use a counta function but I noticed that there were years that had no sales.

I know what I need to do but not sure how to do it.

Get the column number of the last recorded sale, minus the min column of a recorded sale.

Any ideas?

Values are in columns C:O

 

[texasalynn]

post a small sample of your data

 

[dversloot1]

	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013
ASSY303R447_12F									2
ASSY303R449_9F									8
ASSY303R454_9F									7
ASSY303R455_5F								5
BUNDBAVARBUNDPKG							4
BUNDFUVARBUNDPKG							2	1
CABL012R1585					161
CABL110PATCH12A		-1
CABL5VPOWERTAP		8	7	3
CABL699000211								60
CABLBLANKPATCH32		37	12	2
CABLBLANKPATCH48		0
CABLCABLSIGN		4	1
CABLCC10MM		-34	-9	-23		26	-1

<tbody>
</tbody>

 

[dversloot1]

I was able to come up with this by doing some more research. It seemed to calculate what I wanted.

=MAX(IF(C2:O2<>"",$C$1:$O$1))-MIN(IF(C2:O2<>"",$C$1:$O$1))+1
Cntl+Shift+Enter

 

[texasalynn]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	#years
2	ASSY303R447_12F									2					1
3	ASSY303R449_9F									8					1
4	ASSY303R454_9F									7					1
5	ASSY303R455_5F								5						1
6	BUNDBAVARBUNDPKG							4							1
7	BUNDFUVARBUNDPKG							2	1						2
8	CABL012R1585					161									1
9	CABL110PATCH12A		-1												1
10	CABL5VPOWERTAP		8	7	3										3
11	CABL699000211								60						1
12	CABLBLANKPATCH32		37	12	2										3
13	CABLBLANKPATCH48		0												1
14	CABLCABLSIGN		4	1											2
15	CABLCC10MM		-34	-9	-23		26	-1							6
Sheet3

Cell Formulas
Range	Formula
O2	{=INDEX($B$1:$N$1,1,MATCH(9.99999999999999E+307,B2:N2))-INDEX($B$1:$N$1,1,MATCH(TRUE,B2:N2<>"",0))+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dversloot1]

That works too!
Thanks for posting a different solution.