how do I Count repeating numbers in the previous column?
 

I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I just
need to create a formula that will Count the numbers repeated in column A and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

You want Countif. In b1, put:
=countif(a:a,a1)
Copy down to the end of column A (you can do this by double-clicking on the
fill handle).

If you want the entry in column B to appear only when there's a change in
column A, use something like:
=if(a1=a2,"",countif(a:a,a1))

Regards,
Fred

"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I
just
need to create a formula that will Count the numbers repeated in column A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

Try this:

=IF(A2=A3,"",COUNTIF(A$2:A$12,A2))

Copy down as needed

--
Biff
Microsoft Excel MVP


"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I
just
need to create a formula that will Count the numbers repeated in column A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

Not very good at some of these formulas. Can you write the formula using the
data in my example?

"Fred Smith" wrote:

You want Countif. In b1, put:
=countif(a:a,a1)
Copy down to the end of column A (you can do this by double-clicking on the
fill handle).

If you want the entry in column B to appear only when there's a change in
column A, use something like:
=if(a1=a2,"",countif(a:a,a1))

Regards,
Fred

"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I
just
need to create a formula that will Count the numbers repeated in column A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

This worked for the numbers shown. But when I copied it down the next counts
were 15, 6 and 27, but the formula gave me "0" for each. ???

"T. Valko" wrote:

Try this:

=IF(A2=A3,"",COUNTIF(A$2:A$12,A2))

Copy down as needed

--
Biff
Microsoft Excel MVP


"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I
just
need to create a formula that will Count the numbers repeated in column A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

I guess I don't give myself enough credit. I copied over your formula
exactly as below and it worked beautifully! Thank you for all the hours of
work you saved me!!!

"Fred Smith" wrote:

You want Countif. In b1, put:
=countif(a:a,a1)
Copy down to the end of column A (you can do this by double-clicking on the
fill handle).

If you want the entry in column B to appear only when there's a change in
column A, use something like:
=if(a1=a2,"",countif(a:a,a1))

Regards,
Fred

"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I
just
need to create a formula that will Count the numbers repeated in column A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

You have change/adjust the cell references to suit your application. The
formula I posted is based on the sample data you posted.

If your data is in the range A2:A1000 then:

=IF(A2=A3,"",COUNTIF(A$2:A$1000,A2))

--
Biff
Microsoft Excel MVP


"Texas Nuckols" wrote in message
...
This worked for the numbers shown. But when I copied it down the next
counts
were 15, 6 and 27, but the formula gave me "0" for each. ???

"T. Valko" wrote:

Try this:

=IF(A2=A3,"",COUNTIF(A$2:A$12,A2))

Copy down as needed

--
Biff
Microsoft Excel MVP


"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do
the
same thing for a file of 5000 lines. The file is sorted by "Market".
I
just
need to create a formula that will Count the numbers repeated in column
A
and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

Your data appears to already be sorted by market number, inteh furture you
might try the subtotal feature, just change from default of "sum" to "count".

HTH

"Texas Nuckols" wrote:

I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I just
need to create a formula that will Count the numbers repeated in column A and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

PIVOT table is a right tool for this. No formula is required.


"Texas Nuckols" wrote:

I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do the
same thing for a file of 5000 lines. The file is sorted by "Market". I just
need to create a formula that will Count the numbers repeated in column A and
put the answer in column B. Can you help?

how do I Count repeating numbers in the previous column?
 

Glad I was of assistance. I'm glad you got it working. In the future, if you
want "Can you write the formula using the data in my example?", you need to
tell us *where* your data is. It's true you told us your data was in column
A, but you didn't say what row it started on. So we either take our best
guess, or give examples which you have to modify to suit your situation.

We would much rather give the exact formula, but are more often than not
stymied by the lack of information in the original post.

Regards,
Fred.


"Texas Nuckols" wrote in message
...
I guess I don't give myself enough credit. I copied over your formula
exactly as below and it worked beautifully! Thank you for all the hours
of
work you saved me!!!

"Fred Smith" wrote:

You want Countif. In b1, put:
=countif(a:a,a1)
Copy down to the end of column A (you can do this by double-clicking on
the
fill handle).

If you want the entry in column B to appear only when there's a change in
column A, use something like:
=if(a1=a2,"",countif(a:a,a1))

Regards,
Fred

"Texas Nuckols" wrote in message
...
I have this set up:

Market Count
1501
1501
1501
1501
1501 5
1506
1506
1506 3
1507
1507
1507 3

I did this manually for a file of over 1000 lines. Now I need to do
the
same thing for a file of 5000 lines. The file is sorted by "Market".
I
just
need to create a formula that will Count the numbers repeated in column
A
and
put the answer in column B. Can you help?