vlookup plus sumif formula not working

need to get this formula to work to finaly complete my excel file.
the ones in green are without the SUMIF added to the formula
the ones in orange is my difference between the two lines to check if the formula works
the ones in yellow work with adding the SUMIF to the VLOOKUP formula
the ones in RED do not work and for the life of me i can not work out why
PLEASE HELP
thank you

sorry
the formula is the same as the one on the right
it should VLOOKUP the away goals for (gf) total from the LEAGUES tab (=VLOOKUP(K25,fulltable,14,1)
and also it should SUMIF all the burnley goals for (gf) from the results page.
it seams it will not add the second half of the formula..
thank you

I think your issue is that most of the values in RESULTS columns Q and R are regarded as text, not numbers, so when you copy them to X:X, they aren't copied as numbers, even though you've attempted to format them that way. Consequently, any summing you do in RESULTS!X:X only considers X12 and X13 as numbers, since only R12 and R13 are considered numbers. Note that =SUM(X4:X13) on RESULTS yields a return of only 2. Your issue is formatting more than faulty formulas.

Have you tried using the SUMIFS formula?

I'm pretty sure it was available in 2007 version of Excel and you can work on multiple criteria ranges.

Follow up to my post above: If you change the formula in RESULTS!X4 from =R4 to =R4+0, then fill down, that should clean up your number issues in RESULTS!X:X.

EDIT: Ditto with Y:Y

the gf cell should add up to 37 as the on the results tab there is 7 goals to add onto it
this formula adds it up to 32...
and the other for the next cell adds it up to 21..

just checked this and it says the whole columns are in number form..

Your/Jason.b75's formulas on FIXTURES are fine. It's the data in RESULTS causing the problem. Your goal values look like numbers, but are treated as text. Try the changes in post#6.

Originally Posted by jono121ukk

just checked this and it says the whole columns are in number form..

Column X is formatted as number, but the formula in column X refers to column R, column R contains numbers formatted as text, so the values in column X will be text unless you use a coercion method such as the one suggested by cantosh in post #6.

Here's an upload with the changes, which might make what Jason and I are proposing clearer. Note that the meaningful changes are in columns G, H, X and Y of RESULTS, which fixes the mathematical issues you were having on FIXTURES.

thank you to all of you
finaly i can finish this predictor!!