Count number of cells until reach the first occurrence of a specific value.

[Holmes12]

Hi There,

I have a row of cells containing text and I need to count the number of cells backwards through this range until I reach the first instance of a specific text. There will be more than one occurrence of this needed text and only the number of cells before the first appearance is required.

Thank you in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use the MATCH function and deduct 1 from the result.

http://www.contextures.com/xlFunctions03.html

 

[Rick Rothstein]

The wording of your question is not clear. The first sentence says "I need to count the number of cells backwards through this range until I reach the first instance of a specific text" which means to me you would start at the last filled cell and count backwards until you found a cell with the word you are looking for in it. However, your last sentence says you want to count "the number of cells before the first appearance" which says to me you want to count from the first filled cells to either (it is not clear to me) the first occurrence of the text you come to counting forward or the last occurrence of the text still counting forward. Perhaps if you list a range of cells (tells us the address) with duplicated text and then tell us the number you want, maybe your question will be clearer.

 

[AlphaFrog]

In this example, the word "No" is in the 3rd position from the end in row 1:1.

	A	B	C	D	E	F	G	H
1	Yes	Yes	No	Yes	Yes	No	Maybe	Yes
2	3

Worksheet Formulas Cell Formula A2 =LOOKUP(2,1/(1:1<>""),COLUMN(1:1)) - LOOKUP(2,1/(1:1="No"),COLUMN(1:1))+1

 

[Holmes12]

Excel Workbook
	A	B	C	D	E	F	G
1	COMP_NAME	2005	2006	2007	2008	2009	Years Since Burnt
2	CBHMA01	Not Burnt	Burnt	Not Burnt	Not Burnt	Burnt	1
3	CBHMA02	Not Burnt	Not Burnt	Not Burnt	Burnt	Burnt	2
Sheet3

Excel 2007

Sorry for the confusion. Looking at the the above example, I need to count backwards until reaching "Not Burnt" in order to determine number of years since burnt. The values in the 'Years since burnt' column are examples of the value required.

 

[AlphaFrog]

	A	B	C	D	E	F	G
1	COMP_NAME	2005	2006	2007	2008	2009	Years Since Burnt
2	CBHMA01	Not Burnt	Burnt	Not Burnt	Not Burnt	Burnt	1
3	CBHMA02	Not Burnt	Not Burnt	Not Burnt	Burnt	Burnt	2

Worksheet Formulas Cell Formula G2 =LOOKUP(2,1/(B2:F2="Not Burnt"),{4,3,2,1,0}) G3 =LOOKUP(2,1/(B3:F3="Not Burnt"),{4,3,2,1,0})

 

[Holmes12]

Thanks Alpha Dog,

Just one last thing, when inserting another column (another year) is there anyway to add that year to the formula automatically.

I've managed to do this with numbers using the following formula:

COLUMN() - 1 - MATCH(9E+307, OFFSET(J2, 0, 1 -COLUMN(), 1, COLUMN() - 1), 1)

but not with text.

 

[Holmes12]

Sorry AlphaFrog that is

 

[Aladin Akyurek]

Hi There,

I have a row of cells containing text and I need to count the number of cells backwards through this range until I reach the first instance of a specific text. There will be more than one occurrence of this needed text and only the number of cells before the first appearance is required.

Thank you in advance.

Assuming that years in the header row sequential...

<TABLE style="WIDTH: 469pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=626><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3868" width=109><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" width=78><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4608" width=130><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 82pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=109>COMP_NAME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=78 align=right>2005</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=78 align=right>2006</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=79 align=right>2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=70 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=130>Years Since Burnt</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>CBHMA01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>CBHMA02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Burnt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>2</TD></TR></TBODY></TABLE>

G2, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,FREQUENCY(IF(B2:F2="Burnt",COLUMN(B2:F2)),
    IF(B2:F2<>"Burnt",COLUMN(B2:F2))))

 

[AlphaFrog]

Just one last thing, when inserting another column (another year) is there anyway to add that year to the formula automatically.

If you include an empty column (column G below) as part of the formula, then when you select colum G and insert a new column, the formula will include the new column because it was inserted in between the range in the formula.

	A	B	C	D	E	F	G	H
1	COMP_NAME	2005	2006	2007	2008	2009		Years Since Burnt
2	CBHMA01	Not Burnt	Burnt	Not Burnt	Not Burnt	Burnt		1
3	CBHMA02	Not Burnt	Not Burnt	Not Burnt	Burnt	Burnt		2

Worksheet Formulas Cell Formula H2 =LOOKUP(2,1/(B2:G2="Not Burnt"),COLUMN(G2)-COLUMN(B2:G2)-1) H3 =LOOKUP(2,1/(B3:G3="Not Burnt"),COLUMN(G3)-COLUMN(B3:G3)-1)