• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique values based on multiple criteria

samantha

New Member
I have a data sheet of 3 columns

ID Number | Depot | Passed Yes/No
12345 East y
45561 West y
12345 East y
33654 North y
12345 East n
62321 South n
62321 South n
52183 East y
I would like to return a count of how many people at Depot passed eg if I choose South it should give me a return of 1 (it should not count the same id number twice but only once.

if I want to see how many people passed at Depot East it should return a value of 2 (even though we have 4 entries for depot east, id number 12345 passed twice but it should count as 1)
 
samantha
Create a Pivot-table from Your data and read Your expected results:
Screenshot 2022-01-23 at 18.29.32.png
Hint: Send a sample Excel-file with some sample data ... next time.
 
samantha
Did You read my reply's the last line?
Have You explain - how would ... Your client ... use formulas?
eg. =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
 
Hi
sorry my client want simple reporting - I tried this formula - COUNTA(UNIQUE((IF('HR Rep'!S1:S698,S45)*IF(Q1:Q698,F45)*IF('HR Rep'!H1:H698,'HR Rep'!H1:H698)))) but I am getting the wrong totals
2​
 
samantha
Seems You did not read my writing ... still missing that 'need-to-be'.
Seems You skipped my how-question too? ... simple won't be an answer.
I offered something and You wrote something else ...
If I would test Your formula ... then it won't work at all for few reasons - my file, Your used syntax ...
 
@samantha , I think @vletm is trying to get you to attach a workbook.
While you're probably unfamiliar with pivot tables it is the most robust and flexible solution.
In the attached is a dropdown to choose your depot in cell E2, and in cell F2 a formula:
Code:
=IFERROR(ROWS(UNIQUE(FILTER(A2:C9,(B2:B9=E2)*(C2:C9="y")))),0)
Also a pivot table at cell O1, which you can use with or without the slicer.
 

Attachments

  • Chandoo47462.xlsx
    98.9 KB · Views: 3
Back
Top