Forumla for if unique text, then count

[Moomin_dragon]

Hello,
I have a spreadsheet of people, with a status column, an assigned staff member and a postcode column. I want to know how many are at each status by household (not individual), but also my the staff member. E.g, how many households are at awaiting interview (status) for Joe Bloggs (assigned staff member). I have tried combining a working COUNTA unique formula with the working COUNTIFS, and many other combinations but I am getting nowhere, can anybody help? Separate formulas.

=COUNTIFS(Applicants!O1:O156,"<>",Applicants!K1:K156,(C5))

=COUNTA(UNIQUE(Applicants!J2:J100))
OR
=SUMPRODUCT(((Applicants!J2:J168<>"")/COUNTIF(Applicants!J2:J168, Applicants!J2:J168 &"")))

Thanks in advance
Beth

 

[jasonb75]

Maybe

=COUNTA(UNIQUE(FILTER(Applicants!J2:J100,(Applicants!O1:O156<>")*(Applicants!K1:K156=C5))))

 

[Moomin_dragon]

=COUNTA(UNIQUE(FILTER(Applicants!J2:J100,(Applicants!O1:O156<>")*(Applicants!K1:K156=C5))))

Thanks for your response - unfortunately that just results in a '1' no matter what data is there

 

[jasonb75]

When I copied your formulas, I hadn't noticed that the ranges were inconsistent. I've fixed that and changed it so that it doesn't give an incorrect count of 1 when there are no rows that meet the criteria.
This will count unique records in column J where column O is not blank and column K is equal to C5, which is how I understood the requirement from the first 2 formulas in your question.

=IFERROR(ROWS(UNIQUE(FILTER(Applicants!J1:J156,(Applicants!O1:O156<>")*(Applicants!K1:K156=C5)))),0)

 

[Moomin_dragon]

When I copied your formulas, I hadn't noticed that the ranges were inconsistent. I've fixed that and changed it so that it doesn't give an incorrect count of 1 when there are no rows that meet the criteria.
This will count unique records in column J where column O is not blank and column K is equal to C5, which is how I understood the requirement from the first 2 formulas in your question.

=IFERROR(ROWS(UNIQUE(FILTER(Applicants!J1:J156,(Applicants!O1:O156<>")*(Applicants!K1:K156=C5)))),0)

That's done it. Thank you so much!