Countif Query

[Icehockey44]

Hi All,

I need to count some data!!

The problem is the data looks like this:

A1
A2
A3
A4
ABC
RAA
RBB
RCC

i only want to count the entries whihc are A1 A2 A3 etc not the ABS.

currently I have entered count anything beginning with A ("A*"), but this is now incorrect!!

This will be part of an existing formula where I am also counting a sencondry and 3rd column to. So am not wanting these other counts to be disrupted!

Also where I am counting those that start with R ("R*") I now need to also count the ABC entries within this number.

Looking forward to your help

Donna

 

[VoG]

Try

=COUNTIF(A1:A8,"A?")

 

[Icehockey44]

Hi VoG,

I have tried your suggestion, and it stills seems to be counting the ABC. Does the "?" mean it will count the number?

cheers for your help

donna

 

[VoG]

It counts A followed by a single character. My simple test:

Excel Workbook
	A	B
1	A1	4
2	A2
3	A3
4	A4
5	ABC
6	RAA
7	RBB
8	RCC
Sheet3

 

[Icehockey44]

ARghh I can't get it too work!!!

I can if I do your example but not from my actual data.

Does it make a difference that where I said its A1, A2 etc I actually have 2 letters after this data!?! which I think may be throwing my results. (just tried it, and it does).

Is there another way of calculating this?

Sorry about this, didn't think this would affect the formula!!

your help has been fab .

Cheers

donna

 

[VoG]

Can you give some real examples of the codes.

 

[Icehockey44]

Not sure how to pop on attachments. but here is what the data looks like

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A1ab</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64>ROUTE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A2gh</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">STATIC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A3jw</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">ROUTE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A4mm</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">ROUTE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20><?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:stockticker>ABC</st1:stockticker> </TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">STATIC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20><st1:stockticker>CDE</st1:stockticker> </TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">ROUTE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BGH</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">STATIC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ETG</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">STATIC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
I need a count that will tell me the total A? that are route and Static, and the number of Others that Are Route and Static.

Hope this helps

Cheers

Donna

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D
1	A1ab	ROUTE		3
2	A2gh	STATIC
3	A3jw	ROUTE
4	A4mm	ROUTE
5	ABC	STATIC
6	CDE	ROUTE
7	BGH	STATIC
8	ETG	STATIC
Sheet8

 

[Icehockey44]

Wow thats brilliant, worked a treat.

so is this saying count anything that has a number in the 2 place of the string thats greater than 0, and that equals "Route"?

thanks you ever so much for helping on this one, it is very much appreicated.

Cheers

Donna

 

[VoG]

It is counting anything with a number in place 2 in column A and ROUTE in column B.

MID(A1,2,1) returns a text value even if the value is a number like 2.

+0 tries to convert that to a number (if it isn't a number an error value is returned)

ISNUMBER checks if the +0 operation returned a number.

Clear as mud?