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.

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,1,"")))


"tpmax" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tpmax" wrote in message
...
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.

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)?

"Teethless mama" wrote:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,1,"")))


"tpmax" wrote:

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.

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tpmax" wrote in message
...
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.

Check out my suggestion!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"tpmax" wrote in message
...
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)?

"Teethless mama" wrote:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,1,"")))


"tpmax" wrote:

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.

=SUMPRODUCT(LEN(INT(A1:A100))-LEN(SUBSTITUTE(INT(A1:A100),1,"")))


"tpmax" wrote:

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)?

"Teethless mama" wrote:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,1,"")))


"tpmax" wrote:

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.

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Teethless mama" wrote in message
...
=SUMPRODUCT(LEN(INT(A1:A100))-LEN(SUBSTITUTE(INT(A1:A100),1,"")))


"tpmax" wrote:

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)?

"Teethless mama" wrote:

=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,1,"")))


"tpmax" wrote:

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.