Stacked Pins on the same location instead of showing total people for that location

#1

I’m trying to show how many people have selected a city for their current location. However, users are families, and the a representative of each family is signing up for all the members of the family.

My sheet looks like this:

Screen Shot 2020-04-02 at 12.33.50 PM
Screen Shot 2020-04-02 at 12.33.50 PM1756×1072 50.1 KB

It’s displaying like this as a list. You can see it doesn’t add up the Total Travellers per city but that’s because I don’t have a formula in place. Maybe I’ve been awake too long but I can’t figure out how to go about it.

Screen Shot 2020-04-02 at 12.34.01 PM
Screen Shot 2020-04-02 at 12.34.01 PM752×1096 65.8 KB

As a result, I get stacked pins.

Screen Shot 2020-04-02 at 12.36.05 PM
Screen Shot 2020-04-02 at 12.36.05 PM804×1594 162 KB

But the charts were easier to work with:

Screen Shot 2020-04-02 at 12.37.10 PM
Screen Shot 2020-04-02 at 12.37.10 PM754×1014 52 KB

#2

At this point Glides Rollup column does not have the ability to add a Filter condition, so that calculation would need to be done in the sheet with a sumif formula.

#3

I would use a query in the sheet to fill a new tab with unique cities and the sum of all the people. Or like @George_B said, build a new sheet using the UNIQUE formula to get a unique list of cities, then use a SUMIF to total up all the people for that city.