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

Holmes12

New Member
Joined
Sep 18, 2011
Messages
9
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
In this example, the word "No" is in the 3rd position from the end in row 1:1.


ABCDEFGH
1YesYesNoYesYesNoMaybeYes
23


Worksheet Formulas
CellFormula
A2=LOOKUP(2,1/(1:1<>""),COLUMN(1:1)) - LOOKUP(2,1/(1:1="No"),COLUMN(1:1))+1
 
Last edited:
Upvote 0
Excel Workbook
ABCDEFG
1COMP_NAME20052006200720082009Years Since Burnt
2CBHMA01Not BurntBurntNot BurntNot BurntBurnt1
3CBHMA02Not BurntNot BurntNot BurntBurntBurnt2
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.
 
Upvote 0
ABCDEFG
1COMP_NAME20052006200720082009Years Since Burnt
2CBHMA01Not BurntBurntNot BurntNot BurntBurnt1
3CBHMA02Not BurntNot BurntNot BurntBurntBurnt2


Worksheet Formulas
CellFormula
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})
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
=LOOKUP(9.99999999999999E+307,FREQUENCY(IF(B2:F2="Burnt",COLUMN(B2:F2)),
    IF(B2:F2<>"Burnt",COLUMN(B2:F2))))
 
Upvote 0
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.


ABCDEFGH
1COMP_NAME20052006200720082009Years Since Burnt
2CBHMA01Not BurntBurntNot BurntNot BurntBurnt1
3CBHMA02Not BurntNot BurntNot BurntBurntBurnt2


Worksheet Formulas
CellFormula
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top