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]

[TABLE="width: 987"]
<tbody>[TR]
[TD][/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]ASSY303R447_12F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASSY303R449_9F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASSY303R454_9F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASSY303R455_5F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUNDBAVARBUNDPKG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUNDFUVARBUNDPKG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABL012R1585[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]161[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABL110PATCH12A[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABL5VPOWERTAP[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABL699000211[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABLBLANKPATCH32[/TD]
[TD][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABLBLANKPATCH48[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABLCABLSIGN[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CABLCC10MM[/TD]
[TD][/TD]
[TD="align: right"]-34[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-23[/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.