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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael Angelo - 18 Mar 2008 00:55 GMT

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))

Reply to this Message

Pete_UK - 18 Mar 2008 01:39 GMT

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

> 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))

Reply to this Message

Ron Rosenfeld - 18 Mar 2008 02:29 GMT

>Do you mean that you want the (1) to change to (2) automatically as you copy
>the formula across? If so, try this:
[quoted text clipped - 5 lines]
>
>Hope this helps.

That's OK so long as the OP understands that the value will change if he
"moves" the cell to a different column.
--ron

Reply to this Message

Pete_UK - 18 Mar 2008 02:39 GMT

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

> >Do you mean that you want the (1) to change to (2) automatically as you copy
> >the formula across? If so, try this:
[quoted text clipped - 9 lines]
> "moves" the cell to a different column.
> --ron

Reply to this Message

Ron Rosenfeld - 18 Mar 2008 03:08 GMT

>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 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

Reply to this Message

Michael Angelo - 18 Mar 2008 03:13 GMT

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

> Do you mean that you want the (1) to change to (2) automatically as you copy
> the formula across? If so, try this:
[quoted text clipped - 13 lines]
> >
> > =COUNTIF(B$2:B1199,(1)), one being #1, =COUNTIF(B$2:B1199,(2))

Reply to this Message

Pete_UK - 18 Mar 2008 03:20 GMT

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

On Mar 18, 2:13 am, Michael Angelo
<MichaelAng...@discussions.microsoft.com> wrote:

> 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
[quoted text clipped - 21 lines]
>
> - Show quoted text -

Reply to this Message

Ron Rosenfeld - 18 Mar 2008 01:40 GMT

>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))

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

Reply to this Message