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...
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.
Sergei, words are powerless to express my gratitude. THANK YOU SOO MUCH !!!