Forum Discussion
Help with formula
I have a list of cities (column B) and the number of companies (column G) that based in these cities. The column B has duplicates for example:
Canterbury
Canterbury , Kent
Canterbury, Kent
Column D (that represent a number of these companies) linked to the column B:
3
1
39
To make sure that the city located at the specific area, I have added it's latitude and longitude columns (C and D). Having in mind that the latitude and longitude data is identical I'm 100% sure that "Canterbury", "Canterbury , Kent" and "Canterbury, Kent" is the same place. How I can get the total of companies based in Canterbury (43) using columns B, C, D and G? What formula do I have to use? Please direct.
5 Replies
I had the same job to do for a different goal, so I create a list of things to do to accomplish that mission.
I attached the file; this way you don't have to rename all 2000 cities, but it checks it with all the coordinates (which I assume are correct).
Columns in green are the columns you have normally.
Red column is the important one, it merges all the coordinates.
Blue columns are a copy of the green ones, but all datas in that columns are going to be displayed by a formula.
Column J checks if the coordinates are a duplicate, if that so, it leave the cell blank.
All the rest of the columns give something different than an empty cell only if the correspondant J cell is not empty (i.e.: if the row is not a duplicate).
Column P sum all the # of companies of the same coordinates in the first appear of the coordinates.
When you copypaste these formulas on all 2000 rows, you MUST copy columns J to P and paste them as valor (special paste), removing formulas. Then you can delete columns A to I and have a "new table" with some blank rows and the right sum of all the duplicates.
Then sort it or filter it to remove blank rows and the mission is accomplished!
Could be
=SUMIFS(G:G,C:C,INDEX(C:C,MATCH("Canterbury",B:B,0)),D:D,INDEX(D:D,MATCH("Canterbury",B:B,0)))Thank you Sergei, but this won't work. I have attached the spreadsheet for better understanding. Any help would be greatly appreciated.
I think you shall identify you addresses first by one common name for each address. You may add helper column like
=INDEX([Address], MATCH(1,INDEX(([@Easting]=[Easting])*([@Northing]=[Northing]),0,1),0))
which gives one name (first match) for the places with same coordinates.
After that Pivot Table gives you the sum of companies for each place. Please see attached.
