Hello All, I have a list of Department numbers on sheet A (Col A) and need to co

Question

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. Any help 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 Jeffrey 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. Leroy

Explanation / Answer

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 must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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