countif formula for a col and stop if it blanks

[aravindhan_31]

Hi,

can you please help me to put a formula to count the number of entires in columns.

Column A to Column Z (week numbers 1-26)
ColA colB ColC ColD ColE etc...
wk1 Wk2 Wk3 Wk4 Wk5 etc...
#12 #11 #12 #13 #15
#14 #14 #14 ..... #14

from row 2 to 1000 have some data.
i want to put a formula in Column 27 to count how many times a particular data is appeared. eg if i want to know how many times "#12" appeared in column A- Z i can easily put countif like =COUNTIF(A2:Z2,"#12") which will give the number of times the value "#12" appreared, but now i want to know how many times it appreaed continesouly.

eg:
if i want to look for "#14" and if i use countif it would give me 4 as as answer but i want the result as 3 as the entry stops in colD(it will always be blank I had put ...)

All i want to get the number of times that the data appeared continueously

thanks for your help

 

[Andrew Poulsom]

What result would you expect for:

#14 #14 #14 Blank #14 Blank #14 #14

and why?

 

[aravindhan_31]

continues only from begining

so for this

#14 #14 #14 Blank #14 Blank #14 #14

the answer should be 3

 

[Andrew Poulsom]

Maybe:

=MATCH(1,INDEX((A2:Y2="#14")*(B2:Z2=""),),FALSE)-MATCH("#14",A2:Z2,FALSE)+1

 

[Domenic]

Or, maybe...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},COUNTIF(A2:Z2,"#12"),COUNTIF(A2:INDEX(A2:Z2,MATCH(TRUE,A2:Z2<>"#12",0)),"#12")))

 

[aravindhan_31]

Thanks guys

Both of your fomulas gives me same result but i still have a problem in this

if the first entry itself is blank then the result should be nothing..

blank #14 #14 Blank #14 Blank #14 #14

 

[aravindhan_31]

Hi,

used like this and it worked

=IF(A2="","",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},COUNTIF(A2:E2,"#14"),COUNTIF(A2:INDEX(A2:E2,MATCH(TRUE,A2:E2<>"#14",0)),"#14"))))

&

=IF(A2="","",MATCH(1,INDEX((A2:Y2="#14")*(B2:Z2=""),),FALSE)-MATCH("#14",A2:Z2,FALSE)+1)

both the formula works..

thanks guys...