Excel for Mac-Formula

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)


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:




In BP3:




In BQ3:




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!