Counting unique values

[liampog]

Hi

I have 2 columns, Names in Column A and Training Course in Column B.

Sometimes there will be a duplicate Name and Training Course with a different date in Column C.

I'm trying to count the number of UNIQUE names that have taken the Training Course.

So if Joe Bloggs has done the H&S training course 3 times on 3 different dates, I want it to just count him ONCE.

Hope this makes sense.

Thanks
Liam

 

[Joe4]

See here: https://www.extendoffice.com/docume...unique-values-based-on-multiple-criteria.html

 

[liampog]

Hi Joe

Thanks for the reply but it's not quite what I'm after. It might help to include some sample data.

You'll see the total count includes ALL instances of each course and then the unique count only counts ONE instance of someone doing the course .

Hope this helps.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]H&S[/TD]
[TD]10/01/2017[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]H&S[/TD]
[TD]10/02/2017[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]SW[/TD]
[TD]12/03/2017[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]SW[/TD]
[TD]12/04/2017[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]H&S[/TD]
[TD]11/05/2017[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]SW[/TD]
[TD]12/06/2017[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]JBS[/TD]
[TD]15/07/2017[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]JBS[/TD]
[TD]12/08/2017[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]JBS[/TD]
[TD]12/09/2017[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]H&S[/TD]
[TD]12/10/2017[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]SW[/TD]
[TD]12/11/2017[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]JBS[/TD]
[TD]12/12/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COURSE[/TD]
[TD]TOTAL COUNT[/TD]
[TD]UNIQUE COUNT[/TD]
[/TR]
[TR]
[TD]H&S[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SW[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JBS[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

OK, using complex array formulas is really not my strong suit, but I can get the Unique Counts with using a Helper column.
Let's say that your data is in Range A1:C12. Then I added a helper column in column D to identify duplicates.
I placed the following formula in D1 and copied down to D12.

=IF(COUNTIFS(A$1:A1,A1,B1:B$1,B1)>1,"DUP","")

Then, let's say that your total counts are at the bottom, with "H&S" in row 17.
Then place this formula is cell C17 and copy down to C19:

=COUNTIFS(B$1:B$12,A17,D$1:D$12,"<>" & "DUP")

You can even hide the helper column if you do not want to see it.