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

@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!!!

And if we apply this formula?

Formula:

Please Login or Register  to view this content.

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.

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

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!

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

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.