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