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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies