Countif using left

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jul 8, 2002
Messages
11,402
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
Joined
Jul 10, 2006
Messages
452
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 28, 2005
Messages
46,921
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,673
Messages
5,549,351
Members
410,910
Latest member
DessertDiva
Top