Count the number of occasions

**deemo** · 05-22-2009, 11:13 AM

My data is a list of (in reality 3000x larger)

users and the company they belong to. I want to find out how many companies have 1,2,3,4,5 and more than 5 users. Any idea? I am using a pivot table to find out the number of users per company (easy so far) but then I get stuck when I want to use a countif with getpivotdata...

Example of my 2 columns:

user company
user1 a
user2 b
user3 c
user4 b
user5 c

Any help would be great

**windknife** · 05-22-2009, 12:03 PM

Between 1 to 5, you can use following formula
C1

Please Login or Register  to view this content.

Commit with Ctrl+Shift+Enter. then copy down untill C5.

C6 denotes more then 5

Please Login or Register  to view this content.

Also, commit with Ctrl+Shift+Enter

Hope this helps,
windknife

**contaminated** · 05-22-2009, 12:06 PM

I think u can this formula

=COUNT(IF(IF(D2=B1:B35,A1:A35,"")="","",ROW()))

Confirm w/ Ctrl Shift Enter

**DonkeyOte** · 05-22-2009, 12:09 PM

If you're saying that no user is repeated then you can just add a COUNT column @ source such that:

C1: "Heads"
C2: =MIN(6,COUNTIF(B:B,B2)
copied down

Then set up the PT such that:

ROW Field: Company
COLUMN Field: Heads
DATA Field: Users - Count