Help with a COUNTIF formula
 

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

Help with a COUNTIF formula
 

Replace =COUNTIF(BT$3:BT67,"X")
By =COUNTIF(INDIRECT("BT$3:BT"&ROW()),"X")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Victor Delta" wrote in message
...
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

Help with a COUNTIF formula
 

Bernard

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

V

"Bernard Liengme" wrote in message
...
Replace =COUNTIF(BT$3:BT67,"X")
By =COUNTIF(INDIRECT("BT$3:BT"&ROW()),"X")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Victor Delta" wrote in message
...
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