 SOLVED

# 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?

2 Replies
best response confirmed by Mick_Kitcher (New Contributor)
Solution

# Re: 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.

# Re: Excel for Mac-Formula

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!