I have a fantasy golf game with my friends and I have a formula to work out the points (I know it's long, haven't worked out how to use my lookup). There's a part of the formula that requires to count the number of cells using the countif function and then work out the result from there.

I made it an absolute cell reference which works fine. My question is the following:

How do I fill the formula down to the next player and have the countif reference move too?

Fantasy Golf V3.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | G | H | I | ||||

3 | 1 | Richard Hodge | Spieth | 25 | 12 | 1 | 1300 | 6100 | |||

4 | Wallace | 25 | 100 | 3 | 2250 | ||||||

5 | Woodland | 25 | 150 | 6 | 2625 | ||||||

6 | Burns | 25 | 200 | MC | -75 | ||||||

7 | 2 | Jonathan Bonner | Woods | 25 | 60 | 23 | 600 | 752 | |||

8 | D. Johnson | 30 | 14 | 14 | 252 | ||||||

9 | Thomas | 55 | 35 | MC | -100 | ||||||

10 | |||||||||||

Viewers |

Cell Formulas | ||
---|---|---|

Range | Formula | |

G7:G9,G3:G5 | G3 | =VLOOKUP(C3,'Golfers List'!$C$2:$D$37,2) |

H3:H6 | H3 | =IF(G3=1,F3+1000,IF(AND(G3=2),F3*95%,IF(AND(G3=3),F3*90%,IF(AND(G3=4),F3*85%,IF(AND(G3=5),F3*80%,IF(AND(G3>5,G3<11),F3*70%,IF(AND(G3>10,G3<21),F3*60%,IF(AND(G3>20,G3<31),F3*40%,IF(AND(G3>30,G3<41),F3*20%,IF(AND(G3>40,G3<51),F3*10%,IF(AND(G3>50,G3<100),0,IF(G3="MC",-300/COUNTIF($G$3:$G$6,"<>"),IF(G3="WD",0))))))))))))) |

A3,A7 | A3 | =RANK(I3,$I$3:$I$10) |

H7:H9 | H7 | =IF(G7=1,F7+1000,IF(AND(G7=2),F7*95%,IF(AND(G7=3),F7*90%,IF(AND(G7=4),F7*85%,IF(AND(G7=5),F7*80%,IF(AND(G7>5,G7<11),F7*70%,IF(AND(G7>10,G7<21),F7*60%,IF(AND(G7>20,G7<31),F7*40%,IF(AND(G7>30,G7<41),F7*20%,IF(AND(G7>40,G7<51),F7*10%,IF(AND(G7>50,G7<100),0,IF(G7="MC",-300/COUNTIF($G$7:$G$10,"<>"),IF(G7="WD",0))))))))))))) |

I3,I7 | I3 | =SUM(H3:H6) |

Cells with Data Validation | ||
---|---|---|

Cell | Allow | Criteria |

C3:C10 | List | ='Golfers List'!$A$2:$A$322 |