Countif using left

[bsnapool]

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

 

[Richard Schollar]

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]

Is that the only criteria .... the first letter of the cell entries? If so, then:

=COUNTIF(C:N,"C*")

would do it, wouldn't it?

 

[bsnapool]

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]

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]

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