count cells that begin with specific text

in excel how do I count cells that begin with specific text. Ex: in a
column with 100 entries, I want to count the number of cells that have the
letters "app" from the word approved as the first three characters in the cell

=SUMPRODUCT(--(LEFT(A1:A100)="app"))

"mmer at steelcase" wrote:

> in excel how do I count cells that begin with specific text. Ex: in a
> column with 100 entries, I want to count the number of cells that have the
> letters "app" from the word approved as the first three characters in the cell

=COUNTIF(A:A,"app*")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sloth" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(LEFT(A1:A100)="app"))
>
> "mmer at steelcase" wrote:
>
> > in excel how do I count cells that begin with specific text. Ex: in a
> > column with 100 entries, I want to count the number of cells that have
the
> > letters "app" from the word approved as the first three characters in
the cell

this didn't work for me. do the 2 dashes in front of LEFT mean something

"Sloth" wrote:

> =SUMPRODUCT(--(LEFT(A1:A100)="app"))
>
> "mmer at steelcase" wrote:
>
> > in excel how do I count cells that begin with specific text. Ex: in a
> > column with 100 entries, I want to count the number of cells that have the
> > letters "app" from the word approved as the first three characters in the cell

this works since I am looking for the beginning of the text. I thought I
would need to do something with LEFT function. this solution is simpler.
thank you

"Bob Phillips" wrote:

> =COUNTIF(A:A,"app*")
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Sloth" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(LEFT(A1:A100)="app"))
> >
> > "mmer at steelcase" wrote:
> >
> > > in excel how do I count cells that begin with specific text. Ex: in a
> > > column with 100 entries, I want to count the number of cells that have
> the
> > > letters "app" from the word approved as the first three characters in
> the cell
>
>
>

"mmer at steelcase" <[email protected]> wrote in
message news:[email protected]...
> this didn't work for me. do the 2 dashes in front of LEFT mean something
>

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

At first glance, I thought that would work. However, I noticed in your reply
that the ,3 was left out of the left function. If you don't specify the
number of characters to return, left defaults to 1 (if I am not mistaken.)

The -- coerces logical values (true or false) to numbers. As true evaluates
to 1 (and false to 0) -true = -1 (and -false = -0, which of course is 0.)
Applying the negative again changes the -1 to positive 1 and the -0 to 0.

So, although I didn't test it, I would guess if you put ,3 after the 100 in
the formula, it may return the correct result.

BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0)
fwiw.

--
Kevin Vaughn


"mmer at steelcase" wrote:

> this didn't work for me. do the 2 dashes in front of LEFT mean something
>
> "Sloth" wrote:
>
> > =SUMPRODUCT(--(LEFT(A1:A100)="app"))
> >
> > "mmer at steelcase" wrote:
> >
> > > in excel how do I count cells that begin with specific text. Ex: in a
> > > column with 100 entries, I want to count the number of cells that have the
> > > letters "app" from the word approved as the first three characters in the cell

oops, you are absolutely right. Try this.

=SUMPRODUCT(--(LEFT(A1:A100,3)="app"))

sorry.

"Kevin Vaughn" wrote:

> At first glance, I thought that would work. However, I noticed in your reply
> that the ,3 was left out of the left function. If you don't specify the
> number of characters to return, left defaults to 1 (if I am not mistaken.)
>
> The -- coerces logical values (true or false) to numbers. As true evaluates
> to 1 (and false to 0) -true = -1 (and -false = -0, which of course is 0.)
> Applying the negative again changes the -1 to positive 1 and the -0 to 0.
>
> So, although I didn't test it, I would guess if you put ,3 after the 100 in
> the formula, it may return the correct result.
>
> BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0)
> fwiw.
>
> --
> Kevin Vaughn
>
>
> "mmer at steelcase" wrote:
>
> > this didn't work for me. do the 2 dashes in front of LEFT mean something
> >
> > "Sloth" wrote:
> >
> > > =SUMPRODUCT(--(LEFT(A1:A100)="app"))
> > >
> > > "mmer at steelcase" wrote:
> > >
> > > > in excel how do I count cells that begin with specific text. Ex: in a
> > > > column with 100 entries, I want to count the number of cells that have the
> > > > letters "app" from the word approved as the first three characters in the cell

"Kevin Vaughn" <[email protected]> wrote in message
news:[email protected]...
> BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0)
> fwiw.

That is because 0 is neither negative nor positive, so you cannot have -0.
Negative and positive is defined in relation to 0.

Yeah, I should have gone back and rephrased the part where I was talking
about that. My bad.
--
Kevin Vaughn


"Bob Phillips" wrote:

>
> "Kevin Vaughn" <[email protected]> wrote in message
> news:[email protected]...
> > BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0)
> > fwiw.
>
> That is because 0 is neither negative nor positive, so you cannot have -0.
> Negative and positive is defined in relation to 0.
>
>
>