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" <(E-Mail Removed)> wrote in message
news:5022F0E3-9B01-4283-A804-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:5022F0E3-9B01-4283-A804-(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
news:C3C62D53-7C63-4BF9-B774-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:3617FAD2-3DB5-40CC-98A8-(E-Mail Removed)...
> =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.