Total Points

[saspinall]

Hi

I have a football score prediction spreadsheet where people predict english football premier lge scores and I enter them on to a sheet in a single cell per game. I found a formula, on here, that once I put the actual scores in at the top of the column, would give the points for the predicted score. Formula checks the single cell prediction against the single cell actual score and gives either 5, 2 or 0 pts. However when I try to have a running total, as the games are played at different times and days, I get #VALUE! until all the actual scores have been entered. All I do for the total is say =E3+G3+I3+K3+M3+O3+Q3+S3+U3+W3. Any way I can make the total tot up as the games are played as one game is played at dinner, some at 3pm, some at 5.30pm and some on Sunday/Monday.

I used to just put the 2,5 or 0 pts entry in manually, but then found this formula on these threads and works a treat, just dont get a running total. I've attached this weeks sheet for reference.

Thanks in advance


Stu

[zbor]

Here, try this:

this one is better but you need to have Pts on places like in example:

=SUMIF($D$1:$W$1, "Pts", D3:W3)

This one doesn't depend on Pts in header but must have every other column scores

=SUMPRODUCT(E3:V3, MOD(COLUMN($A$1:$R$1), 2))

and pull down

[MarvinP]

Hi Stu,
The problem is in your formula to give the individual points. You need to set them to zero instead of blank. Then summing them will work correctly. See the attached with the fix.

[zbor]

Hi Marvin... You don't need to put "" around 0 if you want number... "0"

[saspinall]

Cheers both of you....I took MarvinP's advice and put zero as the last entry in the pts formula and works a treat, but thanks to all for looking and sorting....cheers...

Stu