# Count each time the same value appears for a person, resetting when a new value is displayed

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.

• 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

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.

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.

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.