Counting Problem

Posted by Elizabeth on December 18, 2001 1:02 PM

I have a list of policy numbers with corresponding claim numbers. In some cases, one policy number might have one or more claims. I'm trying to get a count of those policies with 2 or more claim numbers. Is there a formula that I can use to do this?

Policy # Claim # Count
123456 789456 1
123456 235689
882369 147369
562389 236987 1
562389 669988

Right I'm going through the list manually, but it's taking forever since there are several hundred policy numbers and I'm not 100% sure my totals are accurate.

Thanks!

Posted by tracy28 on December 18, 2001 1:06 PM

Enter this formula to the right of the first row and then copy it down for your entire list:

=COUNTIF(Policy#Range,Policy#)
=COUNTIF(A2:A6,A2)
=2

Then you can sort the entire range by this column descending to get all of the policies with nore than one claim at the top of the list.

Posted by Mark W. on December 18, 2001 1:08 PM

Why not a PivotTable? (nt)