Excel ?, counting rows of text

Factual Questions
1

I have an excel spreadsheet that contains a list of names subsetted by departments.

one line = one row in sprdsht.
For example:
Accounting Department
Sam
Mary
Joe

Security Dept.
Lou
Harry
John
Mike

etc
I need to count the total number of people, ideally by department. The department names do not have any common terms (darn).

Anyone have any ideas?

Thanks in advance

2

There are a lot of ways you can do it if you can re-arrange the spreadsheet. (or a copy of it)

One way would be to add a new column to contain the department name next to each employee. You can insert a column and then put each department name once at the top of the rows and then fill each down into the blank cells in the row below as explained this webpage.

You can then either do a pivot table to get a department by department count (This would be my preferred method. Pivot tables are unbelievably handy). Or use the Countif function to count the number of times a department name appears.

3

If there’s at least one blank line between each department in column A first put an empty line above the first department name, then in cell B2 =if(A1="",-1,B1+1).

Then copy that down column B. This will put a -1 next to each blank cell, a 0 next to each department name, and the number above each later -1 will be the number of people in the department above. You can then either look or retrieve the cells next to zeros for department names and the ones above that for counts.

4

Thanks!
Both excellent ideas. It was late last night and I just couldn’t think of these steps. After a fair night’s sleep, these make good sense.

I appreciate it.