Only Count Unique Values with one Criteria Hello All, I have a list of Departmen

Question

Only Count Unique Values with one Criteria

Hello All,

I have a list of Department numbers on sheet A (Col A) and need to count the number of staff (Col B) for each department. My data is on Sheet B. Problem is on Sheet B names and dept. numbers are duplicated . Dept. 21290 should be 1, 21401 = 3, 2142 = 3 and 21408 = 2. Aany helpp would be appreciated.

SHEET A DEPT STAFF 21290 1 21401 3 21402 3 21408 2 SHEET B Department Last Name First Name 21290 Montalvo Joanna 21290 Montalvo Joanna 21290 Montalvo Joanna 21290 Montalvo Joanna 21401 Hanks Jeffrey 21401 Hanks Jeeffrey 21401 Hanks Jeffrey 21401 Martin Casey 21401 Martin Casey 21401 Montgomery Cecil 21402 Ayala Carlos R. 21402 Ayala Carlos R. 21402 Ayala Carlos R. 21402 Friar Christy 21402 Friar Christy 21402 Herbert Nerisha 21408 Broner Michael 21408 Speaks Jr. Leroy 21408 Speaks Jr. Leroy 21408 Speaks Jr. Leroy 21408 Speaks Jr. Lerroy

Explanation / Answer

Hi,

I have assumed Cols A, B & C on sheet B and col A on Sheet 2.

Put this ARRAY formula in B2, ARRAY enter it, see below for how and drag down.

=COUNT(1/FREQUENCY(IF(Sheet2!$A$2:$A$100=A2,IF(Sheet2!$B$2:$B$100<>"",MATCH(Sheet2!$B$2:$B$100,Sheet2!$B$2:$B$100,0))),ROW($B$1:$B$99)-ROW($B$1)+1))

This is an array formula which mmust be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brrackets
around the formula {}. You can't type these yourself. If yyou edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.