Formula to sum up scores based on letters

[scottj15]

Hi, I'm constructing an excel sheet to look at the recent form of a selection of football teams.

I have 5 cells for each team looking at their last 5 games. W is entered for a win, D for a draw and L for a lose.

Now I want one cell at the end for each team which calculates a score based on their previous 5 games as so:

3 points for a win, 1 for a draw and 0 for a loss.

I wonder if someone could help me with this as I am fairly new to excel.

Any help would be greatly appreciated

Many Thanks!

[oeldere]

yes, that's possible.

If you post your excel file, without confidentional information, i will add the formula's in it.

[Ron Coderre]

With
A1:E1 containing results (W,L, or D)
Example: W, W, L, D, W

This regular formula sums the translated results:
F1: =SUMPRODUCT(LOOKUP(A1:E1,{"D","L","W"},{1,0,3}))

In the above example, the formula returns: 10
3, 3, 0, 1, 3

Is that something you can work with?

[scottj15]

Many Thanks Ron, exactly what I was after!

Thanks for the fast responses