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]

Aaaahhhhhh I finally finished the spreadsheet this morning Jason. Here's it is: (pw=GHS)

http://www.liamsmart.co.uk/downloads/WorldCup2014_v10(working).xls

One very annoying thing I couldn't get my head around is that row() does not return a number when used inside sumproduct(). This made it impossible for me to replicate formula down columns AC & AD, and took a very long time. I had to change the cell references manually 42 times for each of these columns. A big job when you look at my mad formulas. It took ages!

Will replace my nasty formula for column AD with yours when I have the energy to manually change another 42 lines (as yours is a lot better)!

Liam

 

[Shmerty]

I implemented your formula and changed it to the following so I could copy&paste it:

=SUMPRODUCT(--($M$3:$Y$33=INDIRECT("M"&37+(0*34)&":Y"&67+(0*34))),--ISNUMBER(MATCH($M$3:$Y$33,{"H","D","A"},0)))

To explain my use of row(), I was hoping to use this to save me manually editing 42 rows:

=SUMPRODUCT(--($M$3:$Y$33=INDIRECT("M"&37+(ROW()-3*34)&":Y"&67+(ROW()-3*34))),--ISNUMBER(MATCH($M$3:$Y$33,{"H","D","A"},0)))

But I'm still happy. Is there any way to simply the code in AC as your version for AD is so much more maintainable? This is the formula in column AC:

=SUMPRODUCT(($J$3:$X$33=INDIRECT("J"&37+(0*34)&":X"&67+(0*34)))*(ISNUMBER($J$3:$X$33))*(ISNUMBER(INDIRECT("J"&37+(0*34)&":X"&67+(0*34))))*(ISNUMBER(MATCH(COLUMN($J$3:$X$33),{10,12,16,18,22,24},0 ))))

Thanks,
Liam

 

[Shmerty]

I have tried using:

INDEX(ROW()-3,1)

but it returns an N/A error. I found this solution via the webiste excel - ROW() function behaves differently inside SUM() and SUMPRODUCT() - Stack Overflow

Liam

 

[Shmerty]

Also tried

=SUM(ROW()-3*34)

 

[Shmerty]

I done it

I was using the Index() wrong. I now have the code sitting at:

Column AC:

SUMPRODUCT(($J$3:$X$33=INDIRECT("J"&37+(INDEX(ROW()-3,1)*34)&":X"&67+(INDEX(ROW()-3,1)*34)))*(ISNUMBER($J$3:$X$33))*(ISNUMBER(INDIRECT("J"&37+(INDEX(ROW()-3,1)*34)&":X"&67+(INDEX(ROW()-3,1)*34))))*(ISNUMBER(MATCH(COLUMN($J$3:$X$33),{10,12,16,18,22,24},0 ))))

Column AD:

SUMPRODUCT(--($M$3:$Y$33=INDIRECT("M"&37+(INDEX(ROW()-3,1)*34)&":Y"&67+(INDEX(ROW()-3,1)*34))),--ISNUMBER(MATCH($M$3:$Y$33,{"H","D","A"},0)))

 

[Shmerty]

That's me completely finished. Even managed to do an auto-sorting table. I'm so happy with it! Think it looks good as well.

Thank you for everyone's help, especially yours Jason. You have kept me sane with your help. I deeply appreciate that.

Here is my final version: http://www.liamsmart.co.uk/downloads/WorldCup2014_v13(working).xls

I hope to be using it in years to come.

Many (many) thanks,
Liam

 

[jasonb75]

Good to see you got it finished Liam.

Just looking at your latest posts, following your method of using ROW() I would have gone with something like

=SUMPRODUCT(--($M$3:$Y$33=INDEX($M:$M,3+(ROWS(AD$3:AD3)*34)):INDEX($Y:$Y,33+(ROWS(AD$3:AD3)*34))),--ISNUMBER(MATCH($M$3:$Y$33,{"H","D","A"},0)))

The method I used for my version of the sheet was MATCH(first name &" "& last name, C:C,0)

This was how I got the row numbers listed in row 1 of the sorted data sheet, then I just referred to those numbers for the index formula. You will not see this in the sheet though, I used pastespecial to change some formula to values in order to reduce the number of formula in the sheet, this match formula was one of those.