Sort and Count

Posted by Rod on January 27, 2002 2:03 AM

Greetings,
I have a column of 9147 entries (alphanumeric job codes). I've sorted these 9147 jobs by job field (there are about 200 job fields). I need a function that will total the entries per job field (they vary). Tried using the COUNTIF function with negative results, thought about using the DCOUNT function? Am I on the right track?

Thanks for any help.
Rod...

Posted by Aladin Akyurek on January 27, 2002 4:46 AM

Sorting is not needed in order to produce a count per job (job code).
One way to get the counts is by using PivotTables, another by using COUNTIF.

Lets say that A1:C9148 houses your data and jobcodes are in column A. A1:C1 houses the labels (JobCode, etc.).

Activate A2.
Activate Data|Filter|Advanced Filter.
On the Advanced Filter window:
Check "Copy to another location".
Enter in the List Range box: $A$1:$A$9148
Enter in the "Copy to" box: e.g., $F$1
Check "Unique records only".
Click OK.

Now you have in F a list of different/unique jobcodes.
In G2 enter: =COUNTIF($A$1:$A$9148,F2)

Double click on the black square of C2 (lower right corner) in order to copy down the formula in G2 as far as needed.

The formula produces a count of how many times a jobcode recurs in your data.

=============

Posted by Rod on January 27, 2002 12:49 PM

Re: Thanks

Thanks Aladin, I'll edit the worksheet using this method. I think that I'd better bookmark this message boad.