how do I Count repeating numbers in the previous column?

[Texas Nuckols]

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?

 

[Fred Smith]

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

 

[T. Valko]

Try this:

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

Copy down as needed

 

[Texas Nuckols]

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

 

[Texas Nuckols]

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

 

[Texas Nuckols]

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

 

[T. Valko]

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

 

[JR Hester]

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

 

[Teethless mama]

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

 

[Fred Smith]

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.