Excel - need formula to calculate how many times items occurs in list 1

[msmigraine]

I have two columns of data each column has multiple groupings of (A)Job Codes and (B)Job Titles, within each grouping a Job Code can be repeated several times. The formula needs to be able to calculate how many times a job code is repeated and then list under the given group the job code and the number of times it is repeated.

ex: Job Code X = 6
Job Code Y = 2

Countif won't work, the items must appear on the same worksheet - each grouping is separated by 8 empty rows.

I've tried everything I can think of - which probably isn't much B-(

 

[SkipVought]

Hi,

Countif won't work, the items must appear on the same worksheet - each grouping is separated by 8 empty rows.

If data is on multiple sheets, then COUNTIF on each sheet and SUM all the COUNTIF results.

Since you have empty rows between groups, COUNTIF the COLUMN RANGE.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[msmigraine]

Thanks for the quick response Skip,
I'm not able to wrap my head around this?
Here's an example of my data:

JOB CD JOB TITLE

SR MGMT/ADMIN
LI040 SECRETARY II
QV102 UNIT DIR MFG OPS
QV103 UNIT DIR FOC FACT
LI040 SECRETARY II
PA053 DIR PRO QA LC ADI
PU104 DIR QS DEV&DEPLOY
GD014 DIR,DIV SAFTY/ENV/
LI040 SECRETARY II
M1605 PROJECT/PROG MGR
ML072 DIR ADD ENGINEERIN
QV080 DVP LK CNTY STE OP

Do I have to create a countif formula for each job code? This is a very small set of data. Most of the groups contain 100 or more rows. Also, can the "sum" be built into the countif formula?
This is not something I've worked with before?
Kari

 

[SkipVought]

1) Use Advanced Filter on the JobCD COLUMN to gen a UNIQUE RECORDS ONLY list to ANOTHER LOCATION. Use this list as the SOURCE DATA for your counts

2) If you loose the empty rows within you data, you could use the PivotTable Wizard to get [/b]Count of JobCD[/b]

3) Since you have data on multiple sheets (NOT A VERY GOOD DESIGN for data analysis), whatever you do has to be done on EVERY SHEET and then SUMMED in some central location.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244