agwalsh
asked on
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
EE---training-records-vers-03-with-count
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
Saurabh...
Maybe this.....
In H3
In H3
=IF(SUMPRODUCT(--(A$3:A3&[@[Up to date]]=[@[Staff name]]&"Yes"))=1,1,0)ASKER
@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
EE---training-records-vers-03-with-count
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
ASKER
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
EE---training-records-vers-03-with-count
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
EE---training-records-vers-03-with-count
ASKER
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...
ASKER
EE---training-records-vers-03-with-count