I have a performance databse where I have multiple office locations (i.e. O1, O2, O3..) and each office has multiple sales persons (i.e. S1, S2, S3..). Each time a sales person makes a transaction it is stored in the database as a new entry. So the database looks like

O1 S1 abc

O1 S1 bcd

O2 S3 gfh

O1 S2 fghdfh

How can I find out number of sales persons in each office? I am trying to do it with a Pivot. If i put office location and the sales person as value then it gives me the total records. In the above example i would want

O1    2

O2  1

Thanks!!

Hi jtr,

Ashish is right, and we should know the problem in detail to  obtain a solution. So please forward the required file to him.

However, what I have understood from your initial query, the following might help you.
1. Add Office to Column Fields
2. Add Sales Person to Row Fields
3. Add Transaction to Data Fileld
4. This will give you "Count of Transaction"
5. But you need number of sales persons, office-wise
6. So, outside the pivot table area, below each office column, use the following formula
7. =Count(DataRange)

For example, assuming Pivot Table is in range A3 : D8, enter [ =count(B5:B7) ] in cell B10 for office O1, [ =count(C5:C7) ] in cell C10 for office O2 & so on.