count cells that begin with specific text

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

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

the cell

 

[Guest]

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

 

[Guest]

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]

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.

 

[Guest]

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.

 

[Guest]

oops, you are absolutely right. Try this.

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

sorry.

 

[Bob Phillips]

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.

 

[Guest]

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