Home

counting duplicates with several conditions

Johanna Reilly
New Contributor

Hi I have data that looks like this:

IDCity
1Glasgow
2Dundee
3Edinburgh
3Edinburgh
3Dundee
4Glasgow
4Glasgow
4Glasgow
5Edinburgh
6Dundee
6Edinburgh


Each ID is a person and each line is at a specific point in time where the information was collected. What I would like to do is count the number of people of move cities during the time period. So when the ID in column 1 is the same but the city if different. I've tried using COUNTIF but can't seem to figure it out can anyone help with a formula that might do this? 

 

Thanks

 

Jo

3 Replies
For example, what results do you expect to be returned by the formula in Column C?

I just want to find the total number of people who have a change of city on the spreadsheet @Twifoo 

In your sample data, what specific result do you expect as the answer to your question?
Related Conversations