Archery scores: counting some instances but excluding others

[JustyR]

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

 

[Special-K99]

How about

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

 

[Ron Coderre]

MAYBE THIS:

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

Does that help?

 

[JustyR]

Hi,

Thanks for your help.

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?

 

[Von Pookie]

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"))

 

[Mike Blackman]

Hi,

For total points;

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

For Total Hits;

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

 

[JustyR]

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?

 

[Mike Blackman]

Hi,

Had one of my moments back there;

For the CF try;

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

 

[JustyR]

Hi, Mike,

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

I can rest now.