# Count the number of occasions

1. ## Count the number of occasions

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

2. ## Re: Count the number of occasions

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

3. ## Re: Count the number of occasions

I think u can this formula

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

Confirm w/ Ctrl Shift Enter

4. ## Re: Count the number of occasions

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

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

Then set up the PT such that:

ROW Field: Company
DATA Field: Users - Count

5. ## Re: Count the number of occasions

Perfect, really elegant solution. Thanks a lot everyone!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1