# Archery scores: counting some instances but excluding others

#### JustyR

##### New Member
Hi all,

Hope you can help.

I'm trying to count up the number of cells in a row that have data in. This is for the number of arrows that hit the target.

For example, all 12 arrows hit the target and so all of my twelve cells are filled with data. As I'm already using "X" to score as 10, the following formula works great: =COUNTA(B25:G25)+COUNTA(I25:N25)

However, I also want to use the letter "M" to denote a miss. So, say I had two "X"s, two "M"s and another eight numbers, the total hit count would be 10 (10 hits out of a possible 12). I'm struggling with adding that to the formula so that any instance of "M" is not counted in the total number of hits.

Many thanks if you can help,

Justy

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

=COUNTIF(B25:G25,"X")+COUNTIF(B25:G25,">0")+COUNTIF(I25:N25,"X")+COUNTIF(I25:N25,">0")

MAYBE THIS:
Code:
``=COUNTIF(B25:G25,"X")+COUNTIF(I25:N25,"X")``
Does that help?

Hi,

That will only count instances of X. I want to count all instances of all text and numbers, but not when that text is an M.

Is it possible?

There is probably a better formula, but I believe this should get it done:
=(COUNTA(B25:G25)+COUNTA(I25:N25))-(COUNTIF(B25:G25,"M")+COUNTIF(I25:N25,"M"))

Hi,

For total points;

=SUM(B10:M10)+(COUNTIF(B10:M10,"X")*10)

For Total Hits;

=COUNT(B10:M10)+COUNTIF(B10:M10,"X")

Many thanks, Kristy (Von Pookie). That did the trick.

Maybe you can help me with another difficulty along the same lines. I want conditional formatting to occur for cells filled with 9 or X, but of course not when it's an M. I was using "between" 9 and "x", but that doesn't work when an "m" is in the cell.

Any ideas?

Hi,

Had one of my moments back there;

For the CF try;

=OR(A1=9,A1="X")

Hi, Mike,

Thanks for that. So simple when you see it spelt out for you.

I can rest now.

Replies
3
Views
647
Replies
6
Views
284
Replies
0
Views
484
Replies
6
Views
310
Replies
0
Views
574

1,203,607
Messages
6,056,285
Members
444,855
Latest member

### 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