Counting instances of text???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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)?
 
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)
 
Don't know if the OP's subject line is truly descriptive, but this won't
work with text.
 
Back
Top