I have a list of 2000 or so people with a bunch of different titles.
Anyone know how I can get Excel to count the different titles for me?
i.e.
Title A = 35
Title B = 4
Etc?
I’d use a pivot table
Highlight what you want to count, then go to data-pivot table. At the end of the wizard, you should be able to select the columns/rows that you want to count, and set the preferences to count.
(I’d do a simple one on mine to make sure I’m right, but I despise Office XP and haven’t dealt with it enough to figure out the changes.
Brilliant! Thanks!
Or not. I can’t make it work.
Any specifics?
No prob, here’s the simple way:
=countif(range,title)
So if the titles in your database ranged from cell A2 to cell A2000 and the title you wanted to count was “President”, the formula should look like this:
=countif(A2:A2000,“President”)
Happy trails!
Use Access when dealing with so many records.
I agree with the other techniques offered, but here’s a very easy if laborious alternative not involving pivot tables or Access:
1.) Move the column with the titles to one end or the other, then highlight all columns by selecting the title column and dragging across the top of all the columns. All cells but the top-most cell in the title column will be black. Topmost title column cell is white.
2.)Now Select “sort ascending” or “sort descending” (the A-Z or Z-A buttons). Scroll down title column and take a running tally of how many cells have that title. This is fairly easy to do, since they are in alphabetical order.
3.)You can even hit undo twice to return the spreadsheet back to normal once you’re done
Learn to use pivot tables, they are endlessly useful.
Is the title in a column of its own?
Does your table have a title row?