how to count records in a sorted list
 

I have a spread sheet with about 20k users in it sorted by contract # of CU
they belong to. Is there a way I can use the sorted users to provide a total
number of users for each credit union?

Use Pivot table on the data. Take the Credit Union Contract number on the row
and the count of the same field into the data area.

Alok

"Ellen" wrote:

I have a spread sheet with about 20k users in it sorted by contract # of CU
they belong to. Is there a way I can use the sorted users to provide a total
number of users for each credit union?

On Thu, 13 Oct 2005 08:08:19 -0700, "Ellen"
wrote:

I have a spread sheet with about 20k users in it sorted by contract # of CU
they belong to. Is there a way I can use the sorted users to provide a total
number of users for each credit union?

Indeed there is.

Two methods spring to mind.

1. Highlight the whole data set and then Use the Data Subtotal option
from the menu. In the "At each Change in:" box select the field
heading of your CU column, Select the Function "Count", and in the
third option box "Add subtotal to", choose the same CU field heading.

2. The other option, probably simpler and which doesn't need the data
to be sorted, is a COUNTIF formula.

Assuming your Data is in say A1:E20000 with the CU field heading in
A1, Extract a unique list of CU codes with DATA FILTER ADVANCED to say
G1:G...

Then in H1 enter =COUNTIF(A:A,H1) and copy this down column H as
appropriate.

HTH






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 13 Oct 2005 08:37:06 -0700, "Alok"
wrote:

Use Pivot table on the data. Take the Credit Union Contract number on the row
and the count of the same field into the data area.

Good point and far simpler than Subtotal or Countif.

Rgds


Alok

"Ellen" wrote:

I have a spread sheet with about 20k users in it sorted by contract # of CU
they belong to. Is there a way I can use the sorted users to provide a total
number of users for each credit union?

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________