Help with a COUNTIF formula

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

I have an Excel spreadsheet which has client data on successive rows. One of
the columns, BT, has 'X's to indicate a particular feature of some clients
(i.e. one X, or nothing, in the appropriate cell).

To provide a progressive count of the clients with this feature, the next
column has the following formula '=COUNTIF(BT$3:BT67,"X")' - in this case
the formula in cell BU67 (also showing that the client data starts in row
3).

The problem is that I occasionally need to move the client's data to
different rows. When I do so, all the other formulas change their cell
references as I would expect. However, the ones in column BU do not, so in
the above case, if I moved the client in row 67 to say row 60, the formula
in what would be BU60 would remain as '=COUNTIF(BT$3:BT67,"X")'. This mucks
up all the calculations etc.

Is there as way I can modify the formula in col BU so this problem does not
occur?

Many thanks,

V
 
Replace =COUNTIF(BT$3:BT67,"X")
By =COUNTIF(INDIRECT("BT$3:BT"&ROW()),"X")
best wishes
 
Back
Top