Counting departments

[DavidRS]

I have a long list of all the staff and their respective directorates, units and sections. I know the number of directorates and units but i need to know the number of sections. Is there any way to count the number of different names in each cell of a list?

Thanks in advance

 

[shades]

Howdy. A few questions:

Are these columns? (Staff, Directorates, Units, Sections)

Are they filled in completely?

I was confused (not unusual!) about this: "different names in each cell of a list". Do you mean that a cell can contain several names?
________
MARIJUANA INDICA

 

[DavidRS]

Shades,

Sorry - I'm not always good at describing the problem.

Basically, I have a sheet with the names of all our staff and their sections. There are 5 columns (FirstName;LastName;Directorate;Unit;Section) Leaving the names aside for now, the last three columns contain lists of the relevant directorate, unit or section.

e.g. The directorate column has either Chief Exec, Community, Finance or Environment in its cells.

All the cells in each column are filled and are filled with one entry only. What i would like to do is count the number of unique entries and total it at the bottom. I've tried using a pivot table but all I can make it do is count the number of staff in each section but not count the number of sections.

Hope this is this any clearer!

Cheers

 

[shades]

The following formula counts the number of unique values and text found in the range A1:A10. It allows for blank cells:

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""),IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

Note: it is usually not a good idea to put these formulas at the bottom of the columns of data. Rather, if you need it on same area, insert rows above the data, then put the formulas above.
________
Ford Sav History

 

[DavidRS]

Cheers Shades,

I hear you on the formula positioning - I cottoned on to that one after making a mess of a file sometime ago. Apart from anything else it avoids having to scroll to the bottom of a big list just to see the results

 

[DanG]

Or another way to do it is have a list of all the possible directorates and then you can put this to the right of it... =sumproduct(--(a1:a10=f1)) (f1 being the first directorate of the directorates list you made).

I just learned about this and it is very powerfull because you can do things like...
=sumproduct((a1:a10="CEO")*(b1:b10="active")) which should give you the number of "Active" "CEO's".

Also you can reference sheets that are on closed workbooks.

 

[yearightsure]

Another way is to put columns with simple if statements to the side (true=1, false=0) then create a new sheet referencing those columns with a pivot table. Then all that is needed is to right click and refresh.

-yRs

 

[shades]

I just learned about this and it is very powerfull because you can do things like...
=sumproduct((a1:a10="CEO")*(b1:b10="active")) which should give you the number of "Active" "CEO's".

Yes, SUMPRODUCT can be used for multiple condition count (extending COUNTIF), multiple condition SUM (extending SUMIF), can be used to replace ARRAY formulas using SUM(IF, as well as a true SUMPRODUCT
________
Drugtest