Counting instances of text???

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.
 
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)
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

subtract one cell from each cell in a range and count instances 2
Counting the number of occurrences... 2
SUMPRODUCT formula I think w/ wild card & number vs number as text 8
Countif question how do I do this ? 8
Counting coloured cells 3
count - but exclude counting cells with a formula 4
Counting the number of times a character occurs in a cell 1
Counting Filtered Results Using Autofilter 2

Top