Help with a COUNTIF formula

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
 
B

Bernard Liengme

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

Victor Delta

Bernard

Just the job! I knew it was something like that! Very many thanks,

V
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top