SOLVED

Excel for Mac-Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2070980%22%20slang%3D%22en-US%22%3EExcel%20for%20Mac-Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070980%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20i%20need%20a%20formula%20that%20counts%20a%20series%20non%20contiguous%20horizontal%20cells%20which%20are%20formatted%20and%20have%20formulas%20in%20to%20display%20the%20results%20of%20football%20matches%20played%20in%20the%20English%20Premier%20league.%20either%20a%203%20for%20a%20win%2C%201%20for%20a%20draw%20and%20a%200%20for%20a%20loss.%20i%20have%20a%20table%20set%20up%20to%20read%20these%20cells%20and%20then%20interpret%20the%20information%20as%20to%20how%20many%20games%20the%20team%20has%20won%2C%20lost%20and%20drawn%20etc.%20I%20thought%20it%20could%20be%20done%20using%20a%20COUNTIF%20formula%20but%20can't%20manage%20it%3F%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2070980%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2071266%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20for%20Mac-Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2071266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F254736%22%20target%3D%22_blank%22%3E%40Mick_Kitcher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20correct%20the%20differences%20between%20club%20names%20in%20columns%20B%20and%20BM%3A%3C%2FP%3E%0A%3CP%3ELeicester%20Cty%20vs%20Leicester%20City%3C%2FP%3E%0A%3CP%3EMan%20United%20vs%20Man%20Utd%3C%2FP%3E%0A%3CP%3ESheffield%20Utd%20vs%20Sheffieild%20Utd.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20BO3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24D%243%3A%24BK%2442%3D%223%22)*(MOD(COLUMN(%24D%243%3A%24BK%2442)%2C3)%3D0)*(%24B%243%3A%24B%2442%3D%24BM3))%2BSUMPRODUCT((%24D%243%3A%24BK%2442%3D%223%22)*(MOD(COLUMN(%24D%243%3A%24BK%2442)%2C3)%3D0)*(%24B%242%3A%24B%2441%3D%24BM3))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20BP3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24D%243%3A%24BK%2442%3D%221%22)*(MOD(COLUMN(%24D%243%3A%24BK%2442)%2C3)%3D0)*(%24B%243%3A%24B%2442%3D%24BM3))%2BSUMPRODUCT((%24D%243%3A%24BK%2442%3D%221%22)*(MOD(COLUMN(%24D%243%3A%24BK%2442)%2C3)%3D0)*(%24B%242%3A%24B%2441%3D%24BM3))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20BQ3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DBN3-BO3-BP3%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20or%20copy%20down%20from%20BO3%3ABP3%20to%20BO22%3ABP3.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074038%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20for%20Mac-Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Hans%2C%20i%20did%20exactly%20as%20instructed%20and%20yes%20it%20works%20a%20treat!!!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20i%20will%20spend%20time%20now%20understanding%20in%20words%20what%20the%20formula%20does%20but%20for%20now%20it's%20a%20major%20step%20forwards%20and%20has%20given%20me%20insight%20to%20a%20formula%20that%20for%20whatever%20reason%20did%20not%20show%20up%20in%20any%20google%20searches%20that%20i%20have%20carried%20out%20over%20the%20last%20week.%20I%20wish%20I%20had%20come%20to%20the%20forum%20before.%20I%20can't%20thank%20you%20enough%20it%20is%20really%20appreciated!%20You%20may%20see%20me%20here%20again%20in%20the%20future%2C%20take%20care%20my%20friend%20and%20thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
Best Response confirmed by Mick_Kitcher (New Contributor)
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.

@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!