Formula for counting the value if the leftmost character matches

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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

JJ said:
Hi

I have a column of text values and I want to count the values that start
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
2000.
 
Back
Top