Counting a number of certain Char in a certain field that are not in []

[texasryder]

ok here is my problem I'm doing reports for my company on Time cards and the submitted timecards are marked by S and then the name of the person that can accept the timecard. for instance in a cell I have

S [Sho, Sheng] S [Sho, Sheng/ Timecard approval] S [ so on and so forth]

now I can get how many s's but I'm needing 3 as my answer corisponding to the three S's that go before the []

your help would be appriciated

Thanks.

 

[T. Valko]

ok here is my problem I'm doing reports for my company on Time cards and the submitted timecards are marked by S and then the name of the person that can accept the timecard. for instance in a cell I have

S [Sho, Sheng] S [Sho, Sheng/ Timecard approval] S [ so on and so forth]

now I can get how many s's but I'm needing 3 as my answer corisponding to the three S's that go before the []

your help would be appriciated

Thanks.

Maybe you can just count how many [ or ] there are.

=LEN(A2)-LEN(SUBSTITUTE(A2,"]",""))

 

[texasryder]

OK that helps thanks now I need to be able to extend it from a1 to I1. When I replace a1 with a1:i1 it just gives me a #VALUE!

how can I correct this

here is my current formula

=LEN(F10:J10)-LEN(SUBSTITUTE(F10:J10,"]",""))

 

[texasryder]

I think it might be because the fields F through H are blank

is there a way to get around this where I dont have to go in and do one cell at a time

 

[texasryder]

thanks again for your help BIFF

 

[T. Valko]

I think it might be because the fields F through H are blank

is there a way to get around this where I dont have to go in and do one cell at a time

Try it like this...

Book1
	A
2	S [...] S [...] S [...]
3	_
4	X []
5	C [] B []
6	H
7	K [] N [] M [] T []
8	_
9	_
10	_
Sheet1

There are 10 sets of brackets in the range. So, we'll count how many of the closing brackets ] there are.

=SUMPRODUCT(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,"]","")))