# Count unique company names (Frequency Array Formula)

1. ## Count unique company names (Frequency Array Formula)

Dear all,

I would like to count the no. of companies who would not come (RSVP). I have the formula

Formula:
`Please Login or Register  to view this content.`

Organisation Name is column F, S is attend, R is guest type

Guest name are listed in rows as below:

Organisation Name (Eng) Guest Type "Attend
(Y/N)"
A company 6th N
A company 6th Y
A company 6th Y
B company 6th N
B company 6th Y
B company 6th N
C company 6th Y
C company 6th Y
D company 6th N
D company 6th N

However, the formula would calculate those company who would come but with one representative not coming as it would be listed as "N" as well. I only would like to count those companies with all names are of "N". Could anybody advise? Thanks a lot!

Regards,
Carol

2. ## Re: Count unique company names (Frequency Array Formula)

Not real sure what you're wanting to do.

This counts the unique companies that have an N.

Data Range
 A B C D E F 1 ------ ------ ------ ------ ------ ------ 2 A company 6th N N 3 3 A company 6th Y 4 A company 6th Y 5 B company 6th N 6 B company 6th Y 7 B company 6th N 8 C company 6th Y 9 C company 6th Y 10 D company 6th N 11 D company 6th N

This array formula** entered in F2:

=SUM(IF(FREQUENCY(IF(C2:C11=E2,MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

3. ## Re: Count unique company names (Frequency Array Formula)

@Tony,
What is required is number of companies where all (expected) attendees have N. In the example company D. so result is 1.

Beyond my pay scale!!!

4. ## Re: Count unique company names (Frequency Array Formula)

Originally Posted by JohnTopley
@Tony,
What is required is number of companies where all (expected) attendees have N. In the example company D. so result is 1.
OK, try this version...

Data Range
 A B C D E F 1 ------ ------ ------ ------ ------ ------ 2 A company 6th N N 1 3 A company 6th Y 4 A company 6th Y 5 B company 6th N 6 B company 6th Y 7 B company 6th N 8 C company 6th Y 9 C company 6th Y 10 D company 6th N 11 D company 6th N

This array formula** entered in F2:

=SUM(IF(FREQUENCY(IF(C2:C11=E2,IF(COUNTIFS(A2:A11,A2:A11,C2:C11,E2)=COUNTIF(A2:A11,A2:A11),MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

5. ## Re: Count unique company names (Frequency Array Formula)

And if we apply this formula?
Formula:
`Please Login or Register  to view this content.`

6. ## Re: Count unique company names (Frequency Array Formula)

Dear all,

Thank you so much for all your prompt help. In particular, would like to see when counting unique values, are there any difference between the above "match" one and the below one (it's for counting "Y")? Thanks!

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Count unique company names (Frequency Array Formula)

Thanks for your explanation, this is exactly what I meant!

8. ## Re: Count unique company names (Frequency Array Formula)

Originally Posted by Tony Valko
OK, try this version...

Data Range
 A B C D E F 1 ------ ------ ------ ------ ------ ------ 2 A company 6th N N 1 3 A company 6th Y 4 A company 6th Y 5 B company 6th N 6 B company 6th Y 7 B company 6th N 8 C company 6th Y 9 C company 6th Y 10 D company 6th N 11 D company 6th N

This array formula** entered in F2:

=SUM(IF(FREQUENCY(IF(C2:C11=E2,IF(COUNTIFS(A2:A11,A2:A11,C2:C11,E2)=COUNTIF(A2:A11,A2:A11),MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Dear Tony,

I found that the formula does not work when the company name appeared in other guest type, for example, company D would not be counted as not coming if company D is a 5th guest type which is on the list as well. Appreciate your help! Thanks!

9. ## Re: Count unique company names (Frequency Array Formula)

Sorry, I'm lost on this one.

10. ## Re: Count unique company names (Frequency Array Formula)

It is ok, I am seeing what I can do. Thank you so much!!!

11. ## Re: Count unique company names (Frequency Array Formula)

Dear all,

I got it:

I made the criteria: Countif to countifs and included guest type to confine the counting in that category of guest only.

Formula:
`Please Login or Register  to view this content.`

12. ## Re: Count unique company names (Frequency Array Formula)

Good deal. Thanks for the feedback!

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