The Formula you typed contains an error

[Patcheen]

=SUMIF(F7:F44=A62,IF(G7:G44="won"*3),IF(G7:G44="Drew"*1)

f7:f44 is teams that has to match a62

g7:g44 is won, drew, lost if won 3 points and if drew 1 point

the formula above isnt working as when i input it it says "The Formula you typed contains an error" but i cant see where

[MarvinP]

When I type in your formula it stops on the "*3"

=SUMIF(F7:F44=A62,IF(G7:G44="won"*3),IF(G7:G44="Drew"*1)

Start looking there. Also if you click on the Formulas Tab and then on Evaluate, you can step through your formula to find the problem yourself.

http://www.dummies.com/software/micr...2010-formulas/ is an example of the screen you should use.

[JohnTopley]

Try

=SUMPRODUCT(($F$7:$F$44=$A62)*(($G$7:$G$44="Won")*3+($G$7:$G$44="Draw")))

[Patcheen]

Thank you John that works a treat as usual - Thank you

[JohnTopley]

FYI: an alternative ....

=COUNTIFS(F:F,"=" &A62,G:G,"=won")*3+COUNTIFS(F:F,"=" &A62,G:G,"=draw")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Patcheen]

The above works perfect to do what i want but i need it to ignore "Upcoming"

whisch is in column g also upcoming only changes ones a game is finished

=SUMPRODUCT(($F$7:$F$44=$A62)*(($G$7:$G$44="Won")*3+($G$7:$G$44="Draw"))) - where and how would i get it to ignore $G$7:$G$44="upcoming?
"

[63falcondude]

The above works perfect to do what i want but i need it to ignore "Upcoming"

whisch is in column g also upcoming only changes ones a game is finished

=SUMPRODUCT(($F$7:$F$44=$A62)*(($G$7:$G$44="Won")*3+($G$7:$G$44="Draw"))) - where and how would i get it to ignore $G$7:$G$44="upcoming?
"

If the value in column G is "Won" or "Draw" then it won't be "Upcoming" and will therefore be ignored.

[Patcheen]

Thank you but no joy it turns all values to 0

[Patcheen]

its counting the "upcoming" as a draw which is 1 point

[JohnTopley]

Try

=SUMPRODUCT(($F$7:$F$44=$A62)*($G$7:$G$44<>"Upcoming")*(($G$7:$G$44="Won")*3+($G$7:$G$44="Draw")))

[Patcheen]

Thank you but no joy it turns all values to 0

[JohnTopley]

Something is wrong in your posting: we test column G for won or draw so the test for Upcoming is redundant (I was obviously asleep when I read the post)!

And this was pointed out in post #7.

Post a file because the problem it is not as you describe it.

[Patcheen]

it was my fault i had made an error on my part ur original formula works as it should my fault i only realised when i stripped it down after coping the sheet to a new book an then saw the error thank you for every thing