# Help with unique values

I've got a countif function that I only want to count unique values. In the attached file in the data sheet, I've got a column showing unique but I want it to only count a person's name once - instead of twice as it is doing.. I've put the desired result beside it. I'm missing something in my countif but I can't see it...I've tried all sorts of permutations.. the idea is that if someone has done a course recently and it's current for them i.e. it's appearing as Yes under up to date...that should only be counted ONCE - no matter how many times they have done it. Thanks :-)
EE---training-records-vers-03-with-count
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I'm assuming you are looking for Column-H formula..You can apply this..

``````=IF(AND([@[Up to date]]="Yes",SUMPRODUCT((A\$3:A3=A3)*(F\$3:F3="Yes"))=1),1,0)
``````

Saurabh...
Microsoft Excel ExpertCommented:
not sure if i understood your question, but isnt the column with unique header should have a formula like this =IF(AND([@[Up to date]]="yes",COUNTIF(\$A\$3:A3,A3)>1),0,1)
Author Commented:
@Professor JimJam and Saurabh Singh Teotia, I've tried both your solutions and I am attaching a file that shows you the results I get with both of your formulas and then I've got the desired result in a column. (Filtered down) This is tantalisingly close :-) again. it's on the Data sheet.
EE---training-records-vers-03-with-count
Commented:
Help Me understand in row-8 and Row-13 has the same value then why both the unqiue what is the logic for that??

Saurabh...
Excel & VBA ExpertCommented:
Maybe this.....

In H3
``````=IF(SUMPRODUCT(--(A\$3:A3&[@[Up to date]]=[@[Staff name]]&"Yes"))=1,1,0)
``````
and copy down.
Author Commented:
@saurabh...if a person has done a course twice in the last  year - that is still just one person who is up to date in the department, therefore I want them only counted ONCE if the answer is yes for them being up to date even if the answer is YES to the same course twice or more. I tried your version - still not the answer. See it attached. I keep feeling as though it's something relatively small that just needs to be tweaked to get it...
EE---training-records-vers-03-with-count
Excel & VBA ExpertCommented:
I think the following formula is what you are after.....

In H3
``````=IF(SUMPRODUCT((A\$3:A3&[@[Up to date]]=[@[Staff name]]&"Yes")*(B\$3:B3=[@[Course name]]))=1,1,0)
``````
Commented:
But now your desired answer is contradiction to what you said because row-8 and row-13 which basis of desired answer says-1 for both of them but its the same person...
Author Commented:
Sigh, good news. @sktneer - your solution works perfectly for the first course I chose i.e CTG. Then I decided to test it with another course i.e. IMEWS I am getting 1 in my pivot sheet for midwives instead of the 5 I get when I filter it....(in this case Professor JimJam's solution seems to work)
EE---training-records-vers-03-with-count
Excel & VBA ExpertCommented:
You should revise your criteria again.

I think the best way to make four copies of Data sheet by holding down the Ctrl key and dragging the sheet towards right. And in all of your four data sheets apply filter for each course (one course per sheet) in col. B and for Yes in col. F. Then only you will have an idea whether you are getting the correct output in pivot as well.
Commented:
This formula sets 1 for the latest course date, including all the other criteria
``````=IF(MAX(INDEX(([Staff name]=Table1[[#This Row],[Staff name]])*([Course name]=Table1[[#This Row],[Course name]])*([Midwife/Doctor]=Table1[[#This Row],[Midwife/Doctor]])*(Table1[[#This Row],[Up to date]]="Yes")*[Course Date],,))=Table1[[#This Row],[Course Date]],1,0)
``````

Experts Exchange Solution brought to you by