# Countif Query

#### Icehockey44

##### New Member
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.

Donna

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try

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

Hi VoG,

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

donna

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

Excel Workbook
AB
1A14
2A2
3A3
4A4
5ABC
6RAA
7RBB
8RCC
Sheet3

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?

your help has been fab .

Cheers

donna

Can you give some real examples of the codes.

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

Try like this

Excel Workbook
ABCD
1A1abROUTE3
2A2ghSTATIC
3A3jwROUTE
4A4mmROUTE
5ABCSTATIC
6CDEROUTE
7BGHSTATIC
8ETGSTATIC
Sheet8

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

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?

Replies
14
Views
187
Replies
2
Views
157
Replies
21
Views
458
Replies
1
Views
165
Replies
14
Views
368

1,203,326
Messages
6,054,744
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back