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.

 

[Holmes12]

Excel Workbook
	A	B	C	D	E	F	G	H
1	COMP_NAME	2005	2006	2007	2008	2009		YSLB
2	CBHMA01	Not Burnt	Scheduled	Invasive	Not Burnt	Not Burnt		2
3	CBHMA02	Not Burnt	Scheduled	Arson	Not Burnt	Lightning		0
Sheet4

Excel 2007

Thanks AlphaFrog, for interest is the above table possible? Where there are a few different types of burns (5 types) and need to count the number of 'Not Burnt' before reaching one of these types.

 

[Holmes12]

Excel Workbook
	A	B	C	D	E	F	G	H
1	COMP_NAME	2005	2006	2007	2008	2009		YSLB
2	CBHMA01	Not Burnt	Scheduled	Invasive	Not Burnt	Not Burnt		2
3	CBHMA02	Not Burnt	Scheduled	Arson	Not Burnt	Lightning		0
Sheet4

Excel 2007

Thanks AlphaFrog, for interest is the above table possible? Where there are a few different types of burns (5 types) and need to count the number of 'Not Burnt' before reaching one of these types.

 

[villy]

Hi this is my idea...

Sub cnt()
For Each cell In Cells
    If cell.Value = "yes" Then Exit For Else y = y + 1
Next
    MsgBox y
    Cells(y).Select
End Sub

 

[Aladin Akyurek]

Excel Workbook
	A	B	C	D	E	F	G	H
1	COMP_NAME	2005	2006	2007	2008	2009		YSLB
2	CBHMA01	Not Burnt	Scheduled	Invasive	Not Burnt	Not Burnt		2
3	CBHMA02	Not Burnt	Scheduled	Arson	Not Burnt	Lightning		0
Sheet4

Excel 2007



Thanks AlphaFrog, for interest is the above table possible? Where there are a few different types of burns (5 types) and need to count the number of 'Not Burnt' before reaching one of these types.

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

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

 

[Holmes12]

Aladin that is exactly it however, when trying to add another column (to add the next year, e.g. 2010) it isn't automatically added to the formula. Is there anyway to correct that? It needs to be able to be updated every year.

Thanks for your help.

 

[Aladin Akyurek]

Aladin that is exactly it however, when trying to add another column (to add the next year, e.g. 2010) it isn't automatically added to the formula. Is there anyway to correct that? It needs to be able to be updated every year.

Thanks for your help.

Assumption: The data is on Sheet1 (Adjust to suit).

Current range is: A1:H3, the headers included.

Year headers are numbers.

The formula is currently in column H and H1 is not numeric.

Define BigNum by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=9.99999999999999E+307

Define Lcol (from Last numeric Column) as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B$1:$H$1)

Now we can invoke in H2, confirmed with control+shift+enter, and copied down:

=LOOKUP(9.99999999999999E+307,
    FREQUENCY(IF(B2:INDEX(B2:H2,Lcol)="Not Burnt",
      COLUMN(B2:INDEX(B2:H2,Lcol))),
     IF(B2:INDEX(B2:H2,Lcol)<>"Not Burnt",
      COLUMN(B2:B2:INDEX(B2:H2,Lcol)))))

 

[T. Valko]

Aladin that is exactly it however, when trying to add another column (to add the next year, e.g. 2010) it isn't automatically added to the formula. Is there anyway to correct that? It needs to be able to be updated every year.

Thanks for your help.

If you were to put the calculation on the left side like I did in this example then it's a whole lot easier (KISS).

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 34px"><COL style="WIDTH: 66px"><COL style="WIDTH: 68px"><COL style="WIDTH: 55px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">COMP_NAME</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">YSLB</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2005</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2006</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2007</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2008</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">CBHMA01</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Not Burnt</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Scheduled</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Invasive</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Not Burnt</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Not Burnt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">CBHMA02</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Not Burnt</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Scheduled</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Arson</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Not Burnt</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Lightning</TD></TR></TBODY></TABLE>


This array formula** entered in B2 and copied down:

=LOOKUP(100,FREQUENCY(IF(C2:Z2="Not Burnt",COLUMN(C2:Z2)),IF(C2:Z2<>"",IF(C2:Z2<>"Not Burnt",COLUMN(C2:Z2)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

How many years of records do you anticipate in needing?

In the formula I use the range from column C to column Z which will accommodate 24 years worth of records. If you think you will not need that many columns of data then just reduce the last column from Z to a lesser column like say, column R?

 

[Holmes12]

Thanks to all.

Finally got what I desperately needed.

 

[Aladin Akyurek]

Thanks to all.

Finally got what I desperately needed.

That's just fine. In case you opted for the formula I provided in post #16, replace 9.9999999999999E+307 with BigNum.

 

[T. Valko]

Thanks to all.

Finally got what I desperately needed.

Good deal. Thanks for the feedback!