counting occurances of text strings

  • Thread starter Thread starter Gr8gonzo
  • Start date Start date
G

Gr8gonzo

I once thought i knew what i was doing in excel!!

Anyway...here is my problem. I am running a collection of spread sheet
that are revenue trackers for my business. Each sheet represents
client, each line represents one of that clients employees

Some of the sheets have upwards of 300 lines Each day, i need to ente
for every client one of four values. '1' 'R' 'r' or '<blank>

for example

Company X
june 28 June 29
Smith 1 R
Doe r R
Brown 1
------------------------------------
total days 1 1
Total R 0 2
total r 1 0

I need a function in excel 2002 that will count and tabulate ad sho
the number of R's and `r's. Here is the catch. My version is th
french release, and i am contractually obligated to stick with tha
one.

i look forward to anyones help

Thanks,
d
 
Hard coded

=SUMPRODUCT(LEN(B2:C4)-LEN(SUBSTITUTE(B2:C4,"R","")))

where B2:C4 is the range with the values, blanks can be counted with

=COUNTBLANK(B2:C4)

for the other 3 you might want to change the formula and use a cell where
you put the
values you count

=SUMPRODUCT(LEN(B2:C4)-LEN(SUBSTITUTE(B2:C4,F1,"")))

in this case it would be F1 that holds the value

I am not going to translate into French but I submit a link with functions
in
different languages

http://cherbe.free.fr/traduc_fonctions_xl97.html

and since English and French are next to each other it should be a breeze
 
Peo seems to think you could have multipe R's, r's or 1's in a single cell I
believe. I didn't see that in your description, so it seems to me that
countif would be the proper function.
in english

=countif(B3:B5,"R")
=Countif(C3:C5,"R")

as examples.
 
I don't know what I thought, some sort of
thought error that's for sure?
However since he has
both "r" and "R" countif will not differentiate,
maybe

=SUMPRODUCT(--(ISNUMBER(FIND("R",B2:C4))))

--

Regards,

Peo Sjoblom

Tom Ogilvy said:
Peo seems to think you could have multipe R's, r's or 1's in a single cell I
believe. I didn't see that in your description, so it seems to me that
countif would be the proper function.
in english

=countif(B3:B5,"R")
=Countif(C3:C5,"R")

as examples.
Click to expand...
 
Back
Top