COUNTIF with changing criteria

[tsumanii]

Hello,
I am using the formula:
=COUNTIF(A$2:A$7433,"<1")
but need the criteria to change as i copy the formula down the column
ie the next cell down would have
=COUNTIF(A$2:A$7433,"<2")

But when i copy the formula, the criteria stays the same. Is ther
anyway to change this? (It would work equally well for me using th
formula =COUNTIF(A$2:A$7433,1) and so on, just as long as i can chang
the criteria!!

hope someone can help!
cheers
Su

 

[Frank Kabel]

Hi Sue
if you start with your formula in row one try
=COUNTIF(A$2:A$7433,"<" & ROW())

if you start in a different row change the aboe to
=COUNTIF(A$2:A$7433,"<" & ROW()-start_row_number+1)
copy this formula down

 

[A.W.J. Ales]

Tsumanii,

Try =COUNTIF(A$2:A$7433,"<"&ROW()-1).

I give Row() - 1 since you in your example started on row 2 with a value
"<1"

If you start on another row you have to adjust the -1 accordingly.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

if you start in a different row change the aboe to
=COUNTIF(A$2:A$7433,"<" & ROW()-start_row_number+1)
copy this formula down

how about
=COUNTIF(A$2:A$7433,"<" & ROW(A10))

 

[tsumanii]

THANKYOU ALL!!!!
it works a treat :-

 

[Dubbie]

Yes All you need to do

instead of putting the value 1 in the criteria box (ex c18)link it to a
cell on spread sheet.

Next don't protect the cell with $ so when you pull the formula down it will
keep pulling the cell in the criteria down to

ie c18
to c19
to c20

then in c18
type 1
in c19 type =c18+1
an so on so on