Formula for counting the value if the leftmost character matches

[Guest]

H

I have a column of text values and I want to count the values that start with a specific character. For example

1 Cat
2 Dog
3 Donkey
4 Gorilla
5 Monkey

I am trying to count only those that start with the letter d, therefore I expect to get a 2 as my result. I tried the following formulas to no avail. I can't figure out how to set the formula properly:
COUNTIF(LEFT(A1:A5,1), "d"
DCOUNT(A1:A5,,LEFT(A1:A5,1)="d"

I was able to do something similar by calculating all the left values on an intermediary (hidden) column and then calculating from these, but the problem is that the workbook may get updated by other people and the experience in the past is that these people "easily forget" that they have to add/modify the appropriate hidden cells

First, is it possible? If so, how? Please help! BTW, I am using Excel 2000.

 

[Bernie Deitrick]

JJ,

Array enter (enter with ctrl-shift-enter) the formula

=SUM((LEFT(A1:A5,1)="d")*1)

Since this will be updated, you may wnat to start with a larger range:

=SUM((LEFT(A1:A5000,1)="d")*1)

HTH,
Bernie
MS Excel MVP

Hi

I have a column of text values and I want to count the values that start

with a specific character. For example,

A
1 Cats
2 Dogs
3 Donkeys
4 Gorillas
5 Monkeys

I am trying to count only those that start with the letter d, therefore I

expect to get a 2 as my result. I tried the following formulas to no avail.
I can't figure out how to set the formula properly:

COUNTIF(LEFT(A1:A5,1), "d")
DCOUNT(A1:A5,,LEFT(A1:A5,1)="d")

I was able to do something similar by calculating all the left values on

an intermediary (hidden) column and then calculating from these, but the
problem is that the workbook may get updated by other people and the
experience in the past is that these people "easily forget" that they have
to add/modify the appropriate hidden cells.

First, is it possible? If so, how? Please help! BTW, I am using Excel

2000.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(LEFT(A1:A100,1)="A"))

 

[Alan Beban]

=COUNTIF(A1:A5,"d*")

Alan Beban