COUNTIF with changing criteria

  • Thread starter Thread starter tsumanii
  • Start date Start date
T

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
 
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
 
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 *
 
Frank Kabel said:
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
Click to expand...

how about
=COUNTIF(A$2:A$7433,"<" & ROW(A10))
 
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
 
Back
Top