# Countif using left

#### bsnapool

##### Active Member
Hi All

I have a different sets of data which could vary with letters in front of a date E.g.

A B C
C 12/05/2006, S 15/05/2003, 16/08/2006

I am looking to do a countif formulae using the left to count "C" before the date. My range is C:N

Any help would be really appreciated

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Richard Schollar

##### MrExcel MVP
Hi Andrew

You can do this by using wildcards:

=COUNTIF(C1:N1,"C*")

which will count cells in C1:N1 which begin with a C.

#### GlennUK

##### Well-known Member
Is that the only criteria .... the first letter of the cell entries? If so, then:
Code:
``=COUNTIF(C:N,"C*")``
would do it, wouldn't it?

#### bsnapool

##### Active Member
Thanks guys, dont know I never thought of this... Worked, thanks for your help...

Just out of interest... how would this work if another code was "cb" before the date?

Andrew

#### Richard Schollar

##### MrExcel MVP
Andrew

In that case you'd need to change your criteria maybe to "C *" (note the space). It will depend on the structure of your data (ie is a space after the letter valid?). If you need to enter more criteria to ensure the correct result, you will need to consider Sumproduct instead.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks guys, dont know I never thought of this... Worked, thanks for your help...

Just out of interest... how would this work if another code was "cb" before the date?

Andrew
One way might be this:
1. In A1 count the 'CB' entries with =COUNTIF(C:N,"CB*")
2. In A2 count the 'C' entries with =COUNTIF(C:N,"C*")-A1

Replies
3
Views
156
Replies
4
Views
60
Replies
17
Views
274
Replies
1
Views
26
Replies
4
Views
68