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

J

Jett Midknight

Board Regular

Joined

Jan 8, 2014

Messages

110

• Jun 6, 2014

• #2

The reason it is doing this is because empty cells are considered as having 0, so it is counting all of the blanks between 3 and 23, 16 of them, and then couting the blanks in 37 through 57, and treating those as matches

 

Upvote 0

S

Shmerty

New Member

Joined

Oct 30, 2013

Messages

36

• Jun 6, 2014

• #3

Jett Midknight said:

The reason it is doing this is because empty cells are considered as having 0, so it is counting all of the blanks between 3 and 23, 16 of them, and then couting the blanks in 37 through 57, and treating those as matches

Click to expand...

Thanks for your response. I thought it was something like that so I did try:

=SUM(IF(J3:J23<>""=J37:J57<>"",1,0))

But that didn't work. In programming I'd extract only valid numbers, push them into an array, and use a loop to compare each index of the array. But I find Excel a lot more difficult

 

Upvote 0

J

Jett Midknight

Board Regular

Joined

Jan 8, 2014

Messages

110

• Jun 6, 2014

• #4

Try this

=SUMPRODUCT(1*(J3:J23=J37:J57))-COUNTIF(J37:J57,"")

 

Upvote 0

S

Shmerty

New Member

Joined

Oct 30, 2013

Messages

36

• Jun 10, 2014

• #5

Jett Midknight said:

Try this

=SUMPRODUCT(1*(J3:J23=J37:J57))-COUNTIF(J37:J57,"")

Click to expand...

Hi Jett,

This has gotten me closer! Thank you for it. However, it's still not ideal as:

1. it sees 0 and "" as a match.
2. the top part of the SS is live, and I will be filling in the scores as the games are played. The rest of the SS are the predicted scores from those participating in my wee competition. So because no games have been played yet, all players are starting on -11.

This has turned into something very tricky, yet when I thought of the idea I thought it sounded simple to implement. I couldn't have been more wrong!

Thanks for your help though, really appreciate it.

Liam

 

Upvote 0

S

Shmerty

New Member

Joined

Oct 30, 2013

Messages

36

• Jun 10, 2014

• #6

Shmerty said:

1. it sees 0 and "" as a match.

Click to expand...

I figured out a hacky fix for this: enter "-" whislt games have not been played. I dont like it, but it works. Prevents 0 matching "".

Liam

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Jun 10, 2014

• #7

Try

=SUMPRODUCT((J3:J23=J37:J57)*(J3:J23<>"")*(J37:J57<>""))

 

Upvote 0

S

Shmerty

New Member

Joined

Oct 30, 2013

Messages

36

• Jun 11, 2014

• #8

jasonb75 said:

Try

=SUMPRODUCT((J3:J23=J37:J57)*(J3:J23<>"")*(J37:J57<>""))

Click to expand...

This works perfect! I have had to extend it to include another 5 columns and I think there might be a neater solution compared to what I have done?

Code:

=SUMPRODUCT(($J$3:$J$23=J37:J57)*($J$3:$J$23<>"")*(J37:J57<>""))+SUMPRODUCT(($L$3:$L$23=L37:L57)*($L$3:$L$23<>"")*(L37:L57<>""))+SUMPRODUCT(($O$3:$O$33=$O$37:$O$67)*($O$3:$O$33<>"")*(O37:O67<>""))+SUMPRODUCT(($Q$3:$Q$33=$Q$37:$Q$67)*($Q$3:$Q$33<>"")*(Q37:Q67<>""))+SUMPRODUCT(($T$3:$T$29=$T$37:$T$63)*($T$3:$T$29<>"")*(T37:T63<>""))+SUMPRODUCT(($V$3:$V$29=$V$37:$V$63)*($V$3:$V$29<>"")*(V37:V63<>""))

 

Last edited: Jun 11, 2014

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Jun 11, 2014

• #9

Try this one

=SUMPRODUCT((J3:T33=J37:T67)*(ISNUMBER(J3:T33))*(ISNUMBER(J37:T67))*(ISNUMBER(MATCH(COLUMN(J3:T33),{10,12,15,17,20,22},0))))

 

Upvote 0

S

Shmerty

New Member

Joined

Oct 30, 2013

Messages

36

• Jun 12, 2014

• #10

jasonb75 said:

Try this one

=SUMPRODUCT((J3:T33=J37:T67)*(ISNUMBER(J3:T33))*(ISNUMBER(J37:T67))*(ISNUMBER(MATCH(COLUMN(J3:T33),{10,12,15,17,20,22},0))))

Click to expand...

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

 

Upvote 0