Lost Formula

[guitarman]

Hi There . I was wondering if anyone could help me ou here. I had a formula for converting single letters to numbers i.e.W=3 D=1 L=0 it is a form table of players Won, Drawn,Lost
Somehow I have lost the formula it has just disappeared. I think the formula was something like =SUMIF($F6:$F55,"W"=3,"D"=1,"L"=0) but this does not work so I have got it wrong somewhere any ideas.Many thanks for your time and trouble in advance.
Mike

 

[Luke M]

The best way would be to setup a small table somewhere in this layout:
D 1
L 0
W 3

(note 1st column is alphabetical)
Then your formula can be like:
=LOOKUP(F6,TableRange)

This setup gives you lots of flexibility if you need to change the scores/labels.

 

[Mike H..]

Hi There . I was wondering if anyone could help me ou here. I had a formula for converting single letters to numbers i.e.W=3 D=1 L=0 it is a form table of players Won, Drawn,Lost
Somehow I have lost the formula it has just disappeared. I think the formula was something like =SUMIF($F6:$F55,"W"=3,"D"=1,"L"=0) but this does not work so I have got it wrong somewhere any ideas.Many thanks for your time and trouble in advance.
Mike

Hi,

Why would you want to Sum the losses? the sum of any number of losses is zero so they can be ignored. Try this

=COUNTIF(F6:F55,"W")*3+COUNTIF(F6:F55,"D")

 

[Hui]

=SUMPRODUCT(3*(F6:F55="W")+(F6:F55="d"))
or
=SUM(3*(F6:F55="w")+(F6:F55="d")) Ctrl+Shift+Enter

 

[guitarman]

Hi Guys Thanks for your quick response all three do the trick thanks very much
May you all have a good day and a brilliant life once again many thanks
Kind regards
Mike