Forum Discussion
Excel for Mac-Formula
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.
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.
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!
