how do you make the following formula count 1,2,3,

  • Thread starter Thread starter Michael Angelo
  • Start date Start date
M

Michael Angelo

I'm using the following formula, but have to add the number lines manually. I
know there has to be a formula to ease my burden.

=COUNTIF(B$2:B1199,(1)), one being #1, =COUNTIF(B$2:B1199,(2))
 
Do you mean that you want the (1) to change to (2) automatically as you copy
the formula across? If so, try this:

=COUNTIF($B$2:$B1199,COLUMN(A1))

and copy across. The COLUMN(A1) (returning 1) will change to COLUMN(B1),
COLUMN(C1) etc, returning 2, 3 and so on.

Hope this helps.

Pete
 
On Mon, 17 Mar 2008 16:55:01 -0700, Michael Angelo <Michael
I'm using the following formula, but have to add the number lines manually. I
know there has to be a formula to ease my burden.

=COUNTIF(B$2:B1199,(1)), one being #1, =COUNTIF(B$2:B1199,(2))
Click to expand...

I'm not sure what you are trying to do.

In general, to increment a number as you fill down, use this in place of your
number:

ROWS($1:1)

If you are incrementing as you fill across, use this:

COLUMNS($A:A)

--ron
 
Do you mean that you want the (1) to change to (2) automatically as you copy
the formula across? If so, try this:

=COUNTIF($B$2:$B1199,COLUMN(A1))

and copy across. The COLUMN(A1) (returning 1) will change to COLUMN(B1),
COLUMN(C1) etc, returning 2, 3 and so on.

Hope this helps.
Click to expand...

That's OK so long as the OP understands that the value will change if he
"moves" the cell to a different column.
--ron
 
I'm not sure what the OP wants, Ron - mine was a wild guess, but you
seem to have come to a similar conclusion as well. I think you should
point out to him, though, that his count range will change if he
copies the formula down.

Pete
 
I'm not sure what the OP wants, Ron - mine was a wild guess, but you
seem to have come to a similar conclusion as well. I think you should
point out to him, though, that his count range will change if he
copies the formula down.

Pete
Click to expand...

I wasn't sure, either, what he wanted. So I just posted functions which vary
depending on whether he is filling down or across, and figured he would deal
with the range reference issue (or post back with more questions).
--ron
 
Ron / Pete, this fixes part of the problem, but now, it doesn't calculate my
criteria. The # of times a number appears in said column. It does for the
first entrie but not the second and third and so on.
=COUNTIF($B$2:$B1199,COLUMN(A1))= 112
=COUNTIF($B$2:$B1199,COLUMN(A2))= 112, should be 95
 
You must be copying it down, so the column has not changed but the row
has - substitute ROW for COLUMN in the formula, but then your range
will change so you will have to put a $ in front of the 1199 to stop
that changing as you copy down.

Hope this helps.

Pete
 
Back
Top