count if function

[khalil]

hello All, please help in this one

for example;
A1 Chair
A2 Chair
A3 Chair
A4 Chair
A5 Table
A6 Chair
A7 Chair
A8 Chair

what i want is to count how many chairs before the column interrupted with table, after table i want to count the chairs after table,

i want the number to be in one cell

i.e before the word table the cell counts 4 chairs, then after table the cell counts 3 chairs,


thanks in advance

 

[JackDanIce]

In one cell? Do you mean you want to count the number of times the word "Chair" occurs in column A?
Try:

=COUNTIF(A1:A8,"Chair")

 

[khalil]

yes but if the word table found , i want to count number of chairs after the word table

thanks

 

[anandvarma]

This is interesting question

i could able to identify the cell before table, but i could not able to put it in the formula with my limited knowledge.

=INDIRECT("a"&(MATCH("Table",$A$1:$A$8,0)-1))


Excel Gurus will help us

 

[khalil]

thanks

 

[Sanjeev1976]

thanks

Copy the below formula in cell B2 and across to get your desired result

=IF(A3=A2,"",COUNTA($A$1:A2)-SUM($B$1:B1))

 

[khalil]

thanks

it did not work exactly like what i want,

i want ,
in one column from A1 : A100
it counts the same word till one cell has a different word then it stops counting,
it starts counting the same word after that different word,

A1 Chair
A2 Chair
A3 Chair
A4 Table
A5 Chair
A6 Chair

cell b1 reads 1st 3 chairs
then when the word table in cell A4 then cell b1 reads 0

once A5 A6 have the word chair then b1 reads 2

and so on


thanks

 

[Sanjeev1976]

thanks

it did not work exactly like what i want,

i want ,
in one column from A1 : A100
it counts the same word till one cell has a different word then it stops counting,
it starts counting the same word after that different word,

A1 Chair
A2 Chair
A3 Chair
A4 Table
A5 Chair
A6 Chair

cell b1 reads 1st 3 chairs
then when the word table in cell A4 then cell b1 reads 0

once A5 A6 have the word chair then b1 reads 2

and so on


thanks

How do you expect all the results in cell B1 ?

 

[khalil]

then let us say that in b1 always counts chairs after the last word table in the column

thanks

 

[Sanjeev1976]

then let us say that in b1 always counts chairs after the last word table in the column

thanks

Use :

=COUNTA(A100:INDEX(A:A,LOOKUP(2,1/(A1:A100="Table"),ROW(A1:A100))+1))

Hope this meets your requirement.