Is there a formula to calculate the number of rows with two unique values and one specified criteria?

[quicklyman]

I have a situation where I import raw data for training course attendance, but need to establish the number of unique users from a specified department, who attended the courses that took place during the reporting period:

For example,

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]HR[/TD]
[TD]John Smith[/TD]
[TD]MS Word Demo[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Warehouse[/TD]
[TD]Fred Bloggs[/TD]
[TD]MS Excel Demo[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mailroom[/TD]
[TD]Jane Jones[/TD]
[TD]MS Powerpoint Demo[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Finance[/TD]
[TD]Dave Williams[/TD]
[TD]MS Word Demo[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Warehouse[/TD]
[TD]John Smith[/TD]
[TD]MS Word Demo[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Finance[/TD]
[TD]Jane Jones[/TD]
[TD]MS Excel Demo[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Warehouse[/TD]
[TD]Fred Bloggs[/TD]
[TD]MS Excel Demo[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each time I obtain the data I don't know from what videos (column C) will have been viewed, and I don't know what people (column B) would have viewed them. I need to calculate how many people from a specified department (column A) viewed each video, and to ignore any people that viewed the same video twice. So in the above example, if I specified "Warehouse" in the formula, I'd need to get a result of "2" (because Fred Bloggs from the Warehouse viewed the MS Excel Demo, and John Smith from the Warehouse viewed the MS Word Demo. Fred Blogg's additional visit to the MS Excel Demo video shouldn't be counted).

Is there a way to do this via a formula rather than a pivot? It means I can just dump the data in each month and get the stats I and my team need).

Essentially I'm trying to ascertain how many unique visitors from a specified department are visiting each different video ( without the popularity of the video being falsely exaggerated by counting multiple visits by the same person).

Any help appreciated!

 

[FDibbins]

Probably start with a helper column (column D?) to ID the duplicates using =countif($A$1:A1,A1) copied down

Then create a table with departments and use COUNTIFs() to run your count, using D = 1

 

[FlameRetired]

Another way.

Excel 2012
	A	B	C	D	E	F
1	1	HR	John Smith	MS Word Demo	HR	1
2	2	Warehouse	Fred Bloggs	MS Excel Demo	Warehouse	2
3	3	Mailroom	Jane Jones	MS Powerpoint Demo	Mailroom	1
4	4	Finance	Dave Williams	MS Word Demo	Finance	2
5	5	Warehouse	John Smith	MS Word Demo
6	6	Finance	Jane Jones	MS Excel Demo
7	7	Warehouse	Fred Bloggs	MS Excel Demo
8	8
Sheet1

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(($B$1:$B$7=E1)/COUNTIFS($B$1:$B$7,$B$1:$B$7,$C$1:$C$7,$C$1:$C$7,$D$1:$D$7,$D$1:$D$7))