Help with a Countif/Sumif over 2 columns

[StuckwithGeo]

Hello!

I am hoping someone can help

I have tons of data similar to the sample data below. I am trying to figure out how many other lat, longs within the whole list are within 5km of each lat and long. For example, for the lat/long in B2, C2, how many other lat/longs in the whole list are within 5km. I then want to do the same with B3, C3 and B4, C4 etc.
etc.

The formula for the distance between lat/long is: =ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Long[/TD]
[TD]Lat[/TD]
[TD]How many other Long, Lats within 5km?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]0.125884[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]0.115445[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]51.4188[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]0.115445[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]51.6298[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]-0.0743092[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]51.4718[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 65, align: right"]-0.0743092[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[DRSteele]

This should work. You just need to expand the ranges between each latitude and the others (and do the same for longitude), then count how many of those differences are less than or equal to the hurdle (5 in your case) and subtract 1 (because the formula compares each latitude to itself, thereby reporting an extra TRUE).

Are you sure you haven't mixed up the latitudes with the longitudes? Most of those coordinates are in the middle of pirate waters off eastern Africa.

references:
http://www.mrexcel.com/forum/excel-...ongitude-coordinates-find-closest-office.html
http://en.wikipedia.org/wiki/Great-circle_distance
BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)
Latitude And Longitude

	A	B	C	D
Place	Long	Lat
a
b
c
d
e
f

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FCE4D6, align: right"]5[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.125884[/TD]
[TD="align: right"]0.115445[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]51.418800[/TD]
[TD="align: right"]0.115445[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]51.629800[/TD]
[TD="align: right"]-0.074309[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]51.471800[/TD]
[TD="align: right"]-0.074309[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]51.000000[/TD]
[TD="align: right"]0.000000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]51.000000[/TD]
[TD="align: right"]0.010000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

</tbody>

Sheet42​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--((ACOS(COS(RADIANS(90-$C$2:$C$7))*COS(RADIANS(90-C2))+SIN(RADIANS(90-$C$2:$C$7))*SIN(RADIANS(90-C2))*COS(RADIANS($B$2:$B$7-B2)))*6371)<$D$1))-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]