2

I have an Excel spreadsheet with two columns:

Col 1 = Department, Col 2 = Employee Name

I would like to count each time the same department appears for the employee and reset the count when a new department is listed.

I tried the COUNTIF() function where it can count all of the departments for the user, but the counter does not reset.

Below is an example of my data; the green column shows the result I would like to achieve.

Sample Spreadsheet

  • 1
    Will the departments and names always be sorted together like in your example? – cybernetic.nomad Jun 18 '18 at 20:48
  • No. I sorted it myself. However, Scott's solution resolves the challenge I was having with the COUNTIFS() function. – Andrew Jun 19 '18 at 12:50
1

Assuming you’ve shown us Columns A, B and C, if the rows might not be grouped (sorted) as they are in your example, put

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)

in cell C2 and drag/fill down.

2

You don't actually need to do any counting. You just need to compare the current row values with the previous row values, and either increment the previous row's Counter or reset to it to one. Just make sure the names and departments are sorted as shown.

Worksheet Screenshot

Enter this simple formula in C2 and ctrl-enter/copy-paste/fill-down the column:

=IF(OR(A1<>A2,B1<>B2),1,C1+1)

Note that this will also work when the name changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.