Count matches of football scores using =SUM(IF(J3:J23=J37:J57,1,0))

[Shmerty]

Hi everyone.

Id expect the above formula (placed in AA34) to return the number 2. But it doesn't, it returns 19! Way off!

I think I'm close, and as you will see from all the different formula combinations, I have been trying everything!

Hopefully someone can help me out.

Many thanks,
Shmerty

 

[Shmerty]

***I cant see an edit button for the post above so I am having to re-post a finished version of the post...***

Had to change the T's to V's but it worked perfect and much simpler code for me to understand. It's great all the support I've had from this forum. That been said..

I never ask for help before I try something myself (I'm a teacher and expect the same from my pupils), but I am struggling with the formula for the other 2 columns:

In column AB: I would like to see if the player has predicted the correct result. So even if if they predict 1-0 and the game finishes 2-1, their counter in this column would increase +1 as they have predicted a home win. I have tried the following code to no avail

[/COLOR]=SUM(IF(J3:J23=J37:J57,IF(L3:L23=L37:L57,1)))[COLOR=#333333]

In column AC: If they predict the exact outcome of the match (so game finishes 3-3 and they have predicted 3-3) their counter in this column would increase +1. I had tried to implemend AND() into the original code I was given for predicting the correct score but the results are nothing worth posting. Dont think AND() is the solution

Hopefully it is a lot easier for excel formula experts.

Many many thanks,
Liam

 

[jasonb75]

Once you submit a post you only have a 10 minute window in which to edit.

I'm not following the layout of your sheet from the screen cap in your original post, are both tables predictions or does one hold actual results?

Would you be willing to consider a different layout? Your existing sheet design is the reason you need such complex formula for simple results.

 

[jasonb75]

A quick sample of a layout that would be easier to work with

	Actual						Prediction 1
Fixture Date	Home		Away		Result		Home	Away	Result	Score
12/06/2014	Brazil	2	Croatia	0	Home		2	0	Home	5
13/06/2014	Mexico	1	Cameroon	0	Home		1	0	Home	5
	Spain	3	Netherlands	0	Home		2	0	Home	4
	Chile	1	Australia	0	Home		0	0	Draw	1

<tbody>
</tbody>

Result is returned by the formula =IF(C3<>"",INDEX({"Away","Draw","Home"},SIGN(C3-E3)+2),"")

Score is calculated using =IF(C3<>"",(C3=H3)+(E3=I3)+(3*(F3=J3)),"")

The formula for finding the total number of correct predictions will be equally simple.

 

[Shmerty]

Thanks for reply. My screenshot is hard to explain all that is going on so I have uploaded the spreadsheet for you to have a look at. Your ideas have given me ideas of the solution!
http://www.liamsmart.co.uk/downloads/WorldCup2014_v2.xls

You're suggestions look brilliant! I think I understand what you are suggesting:

- I can remove column AD now as 1pt for each goal + 3pts for correct outcome will = 5pts. So I dnt need to try and work out fancy formula to check if both predicted scores.
- I need to an an extra column next to each result to say H/D/A (home, draw, away) and compare those 2 cells to increase tally. (Hopefully I can use the same formula as the formula already given to me).

Thanks again, I can the solution is in sight! I only got 4pts after the 1st game last night

Liam

 

[jasonb75]

I had a bit of time to kill, I've left your original sheet in place and created a new version on sheet 2 that is easier to make calculations from. This was only a quick copy paste and throw in the odd formula effort so it's not going to be perfect but will hopefully give you some ideas if you're still making final tweaks to your version.

Sheet 3 contains a formula based league table that will automatically sort as results are entered into the first sheet.

One assumption that I did make was that there will be a predicted score entered for every person for every game, if that is not the case then any omitted entries will be evaluated as predicting a 0-0 draw, potentially awarding points incorrectly.

https://www.dropbox.com/s/eilzvymue2z58so/Shmerty%20WorldCup2014_v2.xls

 

[Shmerty]

Hi Jason,

Your SS is brilliant! However, it is a bit more complicated tahn I need.

I kind of realised that the table sorting was never going to work whilst it was all combined in one sheet so I was just going to use some conditional formation to highlight the player with the most points.

Im trying to look for your formula to work out Correct Outcome but I'm confused. Thats ALL I have left to do! I have tried changing:

=SUMPRODUCT(($J$3:$X$33=J37:X67)*(ISNUMBER($J$3:$X$33))*(ISNUMBER(J37:X67))*(ISNUMBER(MATCH(COLUMN($J$3:$X$33),{10,12,15,17,20,22},0 ))))

to:

=SUMPRODUCT(($M$3:$Y$33=M37:Y67)*(NOT(ISNUMBER($M$3:$Y$33)))*(NOT(ISNUMBER(M37:Y67)))*(NOT(ISNUMBER(MATCH(COLUMN($M$3:$Y$33),{13,19,25},0 )))))

But I only need to compare 3 columns now (M,S,Y)/(13,19,25) and doing Not(isnumeric()) includes blanks again I think. Anyway it returns 227! Not 4 (which is correct). The following code returns 4, but I cant get it to check multiple columns:

=SUMPRODUCT(--(M3:M23=M37:M57))-(COUNTIF(M3:M23,""))

Aaaarrrrggghhhh! I will keep plugging away though!

Liam

 

[Shmerty]

And

[COLOR=#333333]=SUMPRODUCT(--(M3:M23=M37:M57))-(COUNTIF(M3:M23,""))[/COLOR]

only works because all the H/D/A have been calculated for the first column. As sson as I try an d include a column with no W/D/A it returns a negative number.

Here is the almost complete SS incase anyone cant follow what I'm rambling on about:

http://www.liamsmart.co.uk/downloads/WorldCup2014_v7.xls

 

[Shmerty]

Ahhh I changed it to:

COUNTIF(S37:S67,"")

as these are all filled in!

 

[Shmerty]

Think I have achieved it in a very messy way:

=SUM(SUMPRODUCT(--(M3:M23=M37:M57))-(COUNTIF(M37:M57,"")),SUMPRODUCT(--(S3:S33=S37:S67))-(COUNTIF(S37:S67,"")),SUMPRODUCT(--(Y3:Y29=Y37:Y63))-(COUNTIF(Y37:Y63,"")))

 

[jasonb75]

One alternative that came to mind

=SUMPRODUCT(--(M3:Y33=M37:Y67),--ISNUMBER(MATCH(M3:Y33,{"H","D","A"},0)))

You might struggle to follow how it was done in my alternative version, it was all calculated in the second sheet, then the results returned to the original table.

Your "Correct outcome" figure is pulled from AH81 on the 'sorted data' sheet, you may find it easier to follow from that one cell.