CountIfs excluding Duplicates

[Kare1]

In Col A I have a list of names, Col B a list of many courses, I want to count how many people are due to go on the course, the issue is I have duplicates in my list for the same course (due to the data drop im provided)

A B
Name 1 Course 1
Name 2 Course 1
Name 3 Course 1
Name 4 Course 1
Name 1 Course 2
Name 1 Course 3
Name 1 Course 1
Name 2 Course 1
Name 3 Course 1
Name 4 Course 1

If I use count ifs the number of people who need course 1 is 8, but the actual figure should be 4

Is there a way to use something like count ifs but ignore if its same person, same course? I thought something like sum product with Frequency might work, but im struggling

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Kare1]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the tip, i've now done that, hopefully I will now find a solution

 

[maabadi]

Try this. I supposed your Data is in Row 2 to Row 25. if not change it.

=SUMPRODUCT((1/COUNTIFS(A2:A25,A2:A25,B2:B25,B2:B25)))

 

[navic]

Try this formula

=SUMPRODUCT((($B$2:$B$11=E2))/COUNTIFS($B$2:$B$11,$B$2:$B$11&"",$A$2:$A$11,$A$2:$A$11&""))

 

[Kare1]

Try this. I supposed your Data is in Row 2 to Row 25. if not change it.

=SUMPRODUCT((1/COUNTIFS(A2:A25,A2:A25,B2:B25,B2:B25)))

That looks to work to give me the total non duplicates

How do I get it to break down by each course type? (in a separate table obviously)

 

[Kare1]

Try this formula

=SUMPRODUCT((($B$2:$B$11=E2))/COUNTIFS($B$2:$B$11,$B$2:$B$11&"",$A$2:$A$11,$A$2:$A$11&""))

Looking like this works, Just testing then will confirm

 

[Kare1]

Try this formula

=SUMPRODUCT((($B$2:$B$11=E2))/COUNTIFS($B$2:$B$11,$B$2:$B$11&"",$A$2:$A$11,$A$2:$A$11&""))

Yep looks like this works thanks, it doesnt if I use named ranges though, which I can easily work round

If I wanted to add more criteria eg if col C had completed or a blank if not completed & col D had a date when if passed retraining would be due, how could I add this in? Im thinking it would be in the sum product section something like=SUMPRODUCT((($B$2:$B$11=E2)*($c$2:$c$11="completed"))/COUNTIFS($B$2:$B$11,$B$2:$B$11&"",$A$2:$A$11,$A$2:$A$11&""))

 

[navic]

f I wanted to add more criteria eg if col C...

try

=SUMPRODUCT(((B2:B11=E2)*(C2:C11=E3))/(COUNTIFS(A2:A11,A2:A11,B2:B11,E2,C2:C11,E3)+((B2:B11<>E2)+(C2:C11<>E3))))

also, with named range (data1='A' col, data2='B' col, data3='C' col)

=SUMPRODUCT((data2=E2)*(data3=E3))/(COUNTIFS(data1,data1,data2,E2,data3,E3)+((data2<>E2)+(data3<>E3)))