Excel - Count If Function (Counting Occurences)

[deintinis]

I have a 5,000+ line spreadsheet in Excel. I am trying to count the sum of occurences of each Department in the Department column. I am able to sue the Countif function, but was wondering if there was an easier way to do this. I have 50+ Departments that I need to count the occurence of. Do I have to do a seperate Countif function for each department?

 

[Katy44]

You could sort it be department and then use Data-Subtotals to find a count.

 

[bluedragon2]

Use the SUBTOTAL funstion, or the Subtotal wizard under tools.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

sorry, wizard under data menu.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[henio]

Hi,

try creating a pivot table - your subtotals will all appear together.

Henio

 

[deintinis]

I don't think the subtotal function is what I want for this paticular spreadsheet. It has a few rows of column headings and I get errors when trying to subtotal.

I would like to do the pivot table, but am stuck. I created a pivot table in a new worksheet and used the Department column as my range of cells. I now have my list of departements in the pivot table, but how to I count the # of occurences in which these departements appear on the spreadsheet?

Thanks for the quick responses.

 

[henio]

Hi,

put the department in the row and data areas. In the data area set the function to COUNT of departments.

hth

Henio

 

[deintinis]

Ok, I made a pivot report with the selected range of just the department column. That pulled out all of the department names into a new worksheet. I than created another column along side the pivot table. In that column I wrote a countif formula referencing the Departement column and the departement name. I then autofilled the forumla down and everything seems to be right!
This is probably a strange way of going about this, but it worked so I am going with it.
THanks for your responses!