Counting instances of text???

T

tpmax

I need to count the number of instances of "1" in a cell range. Here's the
catch, the range may contain more than one instance of it in a single cell,
but I need to account for each independently. So one cell may contain 11 and
the next 111, but I need to count the number of times (in this case, 5) that
the value is displayed.
 
R

Ragdyer

With the number you're looking for entered in C1, and the range to count is
A1 to A100, try this:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,C1,"")))/LEN(C1)
 
T

tpmax

Fantastic! Is there an easy way for me to control the function so that it
only counts values to the left of a decimal point (e.g., 11.0101 would only
count 2)?
 
R

Ragdyer

Just realized that your subject line mentions TEXT.

The formula I posted will work with text, BUT ... it will be case sensitive,
meaning,
Tpmax in C1 will *not* count tpmax in A1 to A100.

To make the formula work with text and *not be case sensitive*, try this:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(UPPER(A1:A100),UPPER(C1),"")))/LEN(C1)
 
R

Ragdyer

Don't know if the OP's subject line is truly descriptive, but this won't
work with text.
 
Top