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
agwalshAsked:
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.

Saurabh Singh TeotiaCommented:
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)

Open in new window


Saurabh...
0
ProfessorJimJamCommented:
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)
0
agwalshAuthor 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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Saurabh Singh TeotiaCommented:
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...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this.....

In H3
=IF(SUMPRODUCT(--(A$3:A3&[@[Up to date]]=[@[Staff name]]&"Yes"))=1,1,0)

Open in new window

and copy down.
0
agwalshAuthor 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
0
Subodh Tiwari (Neeraj)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)

Open in new window

0
Saurabh Singh TeotiaCommented:
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...
0
agwalshAuthor 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
0
Subodh Tiwari (Neeraj)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.
0
Ejgil HedegaardCommented:
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)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
@Ejgil...that, my friend, works like a dream. Am very impressed :-) See file below. I've checked all the courses and they match.
EE---training-records-vers-03-with-count
0
agwalshAuthor Commented:
This was a rather tricky formula in that it had to only count something once i.e. a person was up to date even if they had done a course a number of times. Ejgil Hedegaard solved it. But am - as always - very appreciative of what sktneer, Professor JimJam and Saurabh Singh Teotia contributed to the resolution...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.