It just doesn't add up

[tom hatten]

How can I add up the goals scored in three matches when they are presented in the same way as in the attachment? I know how to do it if the information was entered in 4 columns but that would just be more work to do as I have 12 to 15 of these to do, twice a week.
This is how they are copied from my forum and I'm trying to find a way to add up the goals and award 100 points for every goal scored over ten. For example, member A would win 400 points.
Thank you.

[DonkeyOte]

You don't stipulate where/how you want the results recorded... perhaps:

Please Login or Register  to view this content.

[daddylonglegs]

Try this formula for member A

=MAX(0,SUMPRODUCT(MID(A3:A5,FIND("-",A3:A5)+{1,-2},2)+0)-10)*100

You'll get an error if you don't have the scores filled in e.g. Blackburn v West Brom

[solnajeff]

Hi

Attached is a revised version that conforms to your request.

Regards

Jeff

[tom hatten]

Try this formula for member A

=MAX(0,SUMPRODUCT(MID(A3:A5,FIND("-",A3:A5)+{1,-2},2)+0)-10)*100

You'll get an error if you don't have the scores filled in e.g. Blackburn v West Brom

I've tried them both and I like the first one better because I can copy right down column B. I have to remove the box around each cell but I'm not bothered about that.
Will I be able to use the second example (the first one is far to complex for me, lol) if I wanted to subtract goals which is for another game I run?
Thank you so much for your help. I've been spending hours every weekend and midweek to do all this stuff manually.
I'll try and implement some of the formulas you've given me before but I may well be asking for more advice soon to help me save time in the future.
Thank again.

[tom hatten]

Just realised I've had help from two different members. Thank you both very much.

[daddylonglegs]

Just realised I've had help from two different members.

Three in fact

You could revise mine a little if you want to copy down. Try this in B6 copied down

=IF(A6="Points won",MAX(0,SUMPRODUCT(MID(A3:A5,FIND("-",A3:A5)+{1,-2},2)+0)-10)*100,"")

That will still give you an error for A24......you might want an error to alert you to the fact that the scores aren't complete......or would you want to add up the goals anyway?

Note that this version will accommodate unlikely 2 digit scores like 10-3

[tom hatten]

You guys are amazing. I hope you don't mind but I'll be asking for solutions to other games soon because you can do all this stuff in a matter of minutes.
Thank you very much.

[tom hatten]

Hi

Attached is a revised version that conforms to your request.

Regards

Jeff

Thank you! I like this one too but I've noticed that it's giving out the answers in tens and not
hundreds.