Calculating unique number of visitors

[deSelby]

Hi there,

I apologise, as I may be asking a few questions here over the next few hours or so, as I'm not particularly skilled with Excel and have been charged with creating a register at work!

Here's my problem. I need to calculate certain things from a given attendance sheet i.e. Total number of times an individual attended over a course of sessions, the attendance of individual sessions, and the total number of unique attendees over the course of the sessions. Now, the first two I've been able to do no problem using a Countif function (attendance is registered by giving a Y or N value on a drop down menu), but I have no idea where to start with calculating the number of unique attendees.

Could anyone here be of any help?

 

[Jon von der Heyden]

Hi and welcome to the board.

Perhaps;

Excel Workbook
	A	B	C	D
1	Attendee
2	Jon		Unique attendees	4
3	Richard
4	Bob
5	Rory
6	Bob
7	Bob
8	Rory
9	Jon
10	Richard
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A100<>"",MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

where A2:A100 houses the attendee names.

 

[deSelby]

Hi there Jon, thanks for getting back to me so quickly!

I should probably explain myself a little bit better. Here is the worksheet as I currently have it:

It might not be the tidiest way of doing it, but what I was envisaging was some sort of clause whereby if the Total Attendance column had a total greater than 1, I could use a function to total up how many instances of 1 or more attendances was registered.

I'm sorry, that's probably the worst sentence I've ever constructed. I hope that's in some way clear.