Excel for Mac-Formula

[Mick_Kitcher]

Hi, i need a formula that counts a series non contiguous horizontal cells which are formatted and have formulas in to display the results of football matches played in the English Premier league. either a 3 for a win, 1 for a draw and a 0 for a loss. i have a table set up to read these cells and then interpret the information as to how many games the team has won, lost and drawn etc. I thought it could be done using a COUNTIF formula but can't manage it?   

[#2 · Accepted solution]

@Mick_Kitcher 

First, correct the differences between club names in columns B and BM:

Leicester Cty vs Leicester City

Man United vs Man Utd

Sheffield Utd vs Sheffieild Utd.

Enter the following formula in BO3:

=SUMPRODUCT(($D$3:$BK$42="3")*(MOD(COLUMN($D$3:$BK$42),3)=0)*($B$3:$B$42=$BM3))+SUMPRODUCT(($D$3:$BK$42="3")*(MOD(COLUMN($D$3:$BK$42),3)=0)*($B$2:$B$41=$BM3))

In BP3:

=SUMPRODUCT(($D$3:$BK$42="1")*(MOD(COLUMN($D$3:$BK$42),3)=0)*($B$3:$B$42=$BM3))+SUMPRODUCT(($D$3:$BK$42="1")*(MOD(COLUMN($D$3:$BK$42),3)=0)*($B$2:$B$41=$BM3))

In BQ3:

=BN3-BO3-BP3

Fill or copy down from BO3:BP3 to BO22:BP3.

[#3]

@Hans Vogelaar 

Hi Hans, i did exactly as instructed and yes it works a treat!!! :) i will spend time now understanding in words what the formula does but for now it's a major step forwards and has given me insight to a formula that for whatever reason did not show up in any google searches that i have carried out over the last week. I wish I had come to the forum before. I can't thank you enough it is really appreciated! You may see me here again in the future, take care my friend and thanks again!