Help with conditional formula

[cartoony]

I have a spreadsheet that I use for keeping cricket batting averages.
Entries will be something like 24, 0, 47, 98*, dnb, 12, 124, 26, each entered in separate cells. The * means that the player was 'not out', & the dnb stands for 'did not bat', which means he played in the game but didn't bat.
I count the number of matches played in cell I2 with
COUNTA(A2:H2)
I count the number of times the player was 'not out' in K2 with
{=SUM(IF(RIGHT(A2:H2,1)="*",1,""))}
I count the number of Innings with
COUNT(A2:H2)+K2 ie the number of numerical values in the range plus the number of not outs which gives the total times he batted
The total number of runs is calculated with
{=SUM((IF(RIGHT(A2:H2,1)="*",VALUE(SUBSTITUTE(A2:H2,"*","")),A2:H2)))} as it needs to 'remove' the asterix from the 'not outs' before adding.
Highest Score is calculated with
{=MAX((IF(RIGHT(A2:H2,1)="*",VALUE(SUBSTITUTE(A2:H2,"*","")),A2:H2)))}
All of the above work OK.

I'm trying to count the number of times a player has scored 50 or more, but it's the conditional formula to get rid of the asterisks that's driving me mad. I'm trying to use
{=COUNTIF((IF(RIGHT(A2:H2,1)="*",VALUE(SUBSTITUTE(A2:H2,"*","")),A2:H2)),">49")} but this returns #value
It's pretty much the same as the other conditionals I've used but I can't work out why SUM works with the conditional but COUNTIF doesn't.

I know I could do this in several separate steps in separate cells, but I'd like to do it in one cell if possible. I can email an example sheet to anyone who might be able to help with the answer. I've posted a copy of the example sheet to: http://rapidshare.com/files/170428005/MrExcelExamplesheet.xls.html
Thanks for your help.

 

[AdamL]

Mate I think COUNTIF interprets "*" as a wildcard unless there is a tilda ("~") before it - however I appreciate your "*" isn't in the criteria part of the COUNTIF, so I don't know if it would really be an issue.

Nevertheless, maybe try

=SUMPRODUCT(--(--SUBSTITUTE(A2:H2,"*","")>49))

(note I don't think an IF statement to see if a "*" is there is necessary)

 

[cartoony]

Thanks for the prompt reply. The formula you provide gives #Value but
=SUMPRODUCT(--(SUBSTITUTE(A2:H2,"*","")>49)) gives me 8 and
=SUMPRODUCT((SUBSTITUTE(A2:H2,"*","")>49)) gives me 0
so it appears that this is counting the cells rather than adding them.
What does the '--' in the formula do? I can't find anything about this in MS Office help.

 

[schielrn]

If you are summing, maybe you want:

=SUMPRODUCT(--(SUBSTITUTE(A2:H2,"*","")>49),A2:H2)

Assuming the value you want to sum is in A2:H2? Hope that helps.

Here is the explanation of the dashes:

http://www.mrexcel.com/forum/showthread.php?t=128907

Sorry I didn't have time to read your original post, so what I posted may not be what you are looking for.

 

[cartoony]

Not quite what i was after - I don't want to sum them but count the number of occurrences where the number is 50 or more.
Also, your formula doesn't add the numbers that have an asterix at the end, i.e. if the source data is 20, 40, 60*, & 80 it returns the result of 140 ( it doesn't add the 60). I want it to count the number of occurences >50, which would be 1. Thank you for your efforts.

 

[Mike Blackman]

Hi,

Try;

=SUMPRODUCT(--(RIGHT(A2:H2,1)="*"),(--(SUBSTITUTE(A2:H2,"*","")>49)))

 

[cartoony]

Still not quite there - that formula reports the number of cells that have an asterix, not the count of numbers >49.
Thanks for the attempt though.

 

[Mike Blackman]

Hi,

Try this;

=SUMPRODUCT(--(RIGHT(A2:H2,1)="*"),--(SUBSTITUTE(A2:H2,"*","")>"49"))

 

[AdamL]

edit

Actually my apologies it's the "dnb" that's throwing things out.

Try (Ctrl+Shift+Enter):

=SUM(IF(ISNUMBER(--SUBSTITUTE(A2:H2,"*","")),--(--SUBSTITUTE(A2:H2,"*","")>49),0))


------------------------------------------------
Originally posted:

My original SUMPRODUCT formula will work, but only if there are no blank cells in the range. If this is the case, try

=SUMPRODUCT(-(--SUBSTITUTE(A2:H2,"*","")>49),-ISNUMBER(A2:H2<>""))

 

[cartoony]

AdamL - you're a star
Worked a treat

Thnak for all your efforts guys