I've created a results page for a board game we play. We enter the results on the Last Game sheet. I then click Save Results which inserts rows and copies the result data accordingly into three sheets, Game Totals, Player 1 and Player 2.
The Last Game sheet has number of wins, averages and such which all use formulas, e.g.
=COUNTIF('Game Totals'!D2:D164, "SUSAN")
D1 is a header row. The latest results are copied into the inserted row, D2.
My problem is while I want D164 to increment to D165 in the formula above I want D2 to remain as D2, but it increments to D3.
I read up a little on using $D$2 but that doesn't stop it from incrementing to D3.
Any help most gratefully appreciated Cheers Johnny.
Assuming that cell 'Game Totals'!D1 does not equal Susan and does not move when you insert rows, you can start the range reference from that cell:
=countif('Game Totals'!D1:D164, "SUSAN")