Counting If Value Shows Up Only Once (not counting unique)

[blahmin027]

I have a list of attendees, and I want to know if the person is a new attendee (meaning never attended any of the webinars since Jan) or is a returning attendee. For example:

David	Webinar 1
Mary	Webinar 2
David	Webinar 2
Pat	Webinar 1
Sam	Webinar 2
Mary	Webinar 3
Mat	Webinar 1
Mat	Webinar 2

I am not counting uniques (David, Mary, Pat, Sam, Mat) but I want to know how many have appeared on my list only once (Pat, Sam).

I am stumped and don't know what formula I should use. Any help will be appreciated

 

[jasonb75]

With names in A2:A9

=SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1))

 

[blahmin027]

Perfect it worked!

My boss now wants two more things:

1. What formula I can use to fill up column C - putting whether the person is New (appears only once in the list) or Returning (appear 2 or more)

David	Webinar 1	Returning
Mary	Webinar 2	Returning
David	Webinar 2	Returning
Pat	Webinar 1	New
Sam	Webinar 2	New
Mary	Webinar 3	Returning
Mat	Webinar 1	Returning
Mat	Webinar 2	Returning

2. What will be the formula if I count the number of those appearing only once PER each webinar?

Webinar 1 = how many who attended this webinar appeared only once in the entire list of all webinars
Webinar 2 = how many who attended this webinar appeared only once in the entire list of all webinars
Webinar 3 = how many who attended this webinar appeared only once in the entire list of all webinars

Sorry for asking by installment. I just emailed my boss and now he wants the sky! LOL Thank you all

 

[jasonb75]

First part, in C2 and fill down

=IF(COUNTIF(A$2:A$9,A2)=1,"New","Returning")

Second part, are you saying that it should still only count Pat and Sam (1 appearance each)?

 

[blahmin027]

Second part -- yes, 1 appearance each in the entire list, no matter what webinar they attended

Thank you so much

 

[jasonb75]

In that case, you should be able to use the results of the New / Returning formula in column C to get what you need.

Book5
	A	B	C
2	David	Webinar 1	Returning
3	Mary	Webinar 2	Returning
4	David	Webinar 2	Returning
5	Pat	Webinar 1	New
6	Sam	Webinar 2	New
7	Mary	Webinar 3	Returning
8	Mat	Webinar 1	Returning
9	Mat	Webinar 2	Returning
10
11	1 Time attendees	2
12	Webinar 1	1
13	Webinar 2	1
14	Webinar 3	0
Sheet12

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIF(A$2:A$9,A2)=1,"New","Returning")
B11	B11	=SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1))
B12:B14	B12	=COUNTIFS(B$2:B$9,A12,C$2:C$9,"New")

 

[blahmin027]

Thank you thank you thank you!