COUNTIF: drag to change criteria, NOT range

[jcmorang]

Hello,

I'm hoping that there's someone out there that can help with this
problem.

I'm trying to count values in one column so that I have an amount of
each for a distribution. For example:

8
8
8
11
11
11
12
12
12

I'm using COUNTIF because it's the only one that seems to work, but
whenever I drag to fill the series Excel doesn't fill in a linear
fashion. Instead it repeats the formulas I've already entered. Here's
an example of what I enter into the cells:

=COUNTIF($A$2:$A$36,8)
=COUNTIF($A$2:$A$36,9)
=COUNTIF($A$2:$A$36,10)

....and so on.

When I highlight these and drag, the sequence is repeated, not
continued to 11, 12, 13 and so on.

FYI, if I don't use the $ sign Excel will change the range from A2 to
A3, A4, A5...as I drag.

I want the criteria to continue (i.e. 8, 9, 10, 11, 12...), not the
range and I don't want it to repeat formulas!

PLEASE HELP!!!!!

Thank you,
Jeff

 

[Pete_UK]

One way to do this is to fill a range with the values you want to count
on, e.g. fill cells F1 to F10 with the values 8, 9, 10, 11, 12, 13, 14,
15, 16, 17. Then your COUNTIF( ) formula can refer to these cells, i.e.

=COUNTIF($A$2:$A$36,F1)

Now when you copy this down you want F1 to change to F2, F3 etc., and
another advantage is that you can change your data without having to
change the formula.

Hope this helps.

Pete

 

[Alan]

With the first formula in row 8 try
=COUNTIF($A$2:$A$36,ROW())
Drag down and it will count the values in the range according to the row
number. If you want to put the formula in row 1,
=COUNTIF($A$2:$A$36,ROW()+7)
It finds the value of the row number and will drag down whereas a number in
the formula wont
Regards,
Alan.

 

[RagDyer]

Try this:

=COUNTIF($A$2:$A$36,ROWS($1:8))

And drag down to copy as needed.

You can enter this anywhere and just have the last number in the formula (8)
designate the *starting* number to count.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Alan]

Thats a good one!, never used that before but will in future,
Regards,
Alan.

 

[jcmorang]

Perfect!!!! You just saved me A LOT of time. It's EXACTLY the result I
wanted.

Thanks RD!!!

 

[jcmorang]

Perfect!!!! You just saved me A LOT of time. It's EXACTLY the result I
wanted.

Thanks RD!!!

Cheers,
Jeff