Sumif within distance from a latitude and longitude pairing

[Mdeco]

Hi there,

I have two lists of coordinates (i.e. latitudes and longitudes), the second of which has a "sales count" associated with it. I want to get a sum of the "sales counts" occurring within x miles of the first list of coordinates. How would you suggest I do this?

See below:

1 A B C D E F G 2 3 Radius 100 4 5 Lat Long Sum Column G if within range B3 Lat 2 Long 2 Sales 6 42.125793 -72.645334 ? 41.377698 -104.660353 1 7 42.184835 -71.947184 ? 43.950896 -110.553181 1 8 42.10273 -72.080996 ? 43.50537 -110.786527 2 9 42.446396 -71.459405 ? 43.50537 -110.786527 3 10 42.504844 -71.201539 ? 43.452793 -110.739266 1 11 42.472112 -70.997794 ? 43.452793 -110.739266 1 12 42.536996 -70.973646 ? 43.452793 -110.739266 1 13 42.347207 -71.086095 ? 44.653104 -107.024713 1 14 42.202216 -71.005192 ? 43.044268 -111.008517 1 15 42.357564 -71.211649 ? 42.785695 -110.186599 1 16 42.379146 -71.184299 ? 43.482361 -110.839782 1 17 41.829813 -70.138834 ? 43.482361 -110.839782 2 <tbody> </tbody>

<tbody>
</tbody>

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

 

[AlanY]

typically the first set of co-ords is over 2500 miles from the second set, thus 100 miles radius won't yield any result unless I interpreted your question incorrectly

 

[AlanY]

this will sum up the sales if set 2 co-ords is within 3175 miles (B3) from set 1

Book1
	A	B	C	D	E	F	G
1
2
3	Radius	3175
4
5	Lat	Long	Sum Column G if within range B3		Lat 2	Long 2	Sales
6	42.12579	-72.6453	16	2641.506	41.3777	-104.66	1
7	42.18484	-71.9472	16	3113.557	43.9509	-110.553	1
8	42.10273	-72.081	16	3132.926	43.50537	-110.787	2
9	42.4464	-71.4594	12	3171.618	43.50537	-110.787	3
10	42.50484	-71.2015	4	3187.696	43.45279	-110.739	1
11	42.47211	-70.9978	2	3204.765	43.45279	-110.739	1
12	42.537	-70.9736	2	3204.727	43.45279	-110.739	1
13	42.34721	-71.0861	3	2886.476	44.6531	-107.025	1
14	42.20222	-71.0052	2	3243.093	43.04427	-111.009	1
15	42.35756	-71.2116	4	3163.091	42.7857	-110.187	1
16	42.37915	-71.1843	4	3200.111	43.48236	-110.84	1
17	41.82981	-70.1388	2	2641.506	43.48236	-110.84	2
Sheet1

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--(ACOS(COS(RADIANS(90-$A6)) *COS(RADIANS(90-$E$6:$E$17)) +SIN(RADIANS(90-$A6)) *SIN(RADIANS(90-$E$6:$E$17)) *COS(RADIANS($B6-$F$6:$F$17))) *6371<$B$3),$G$6:$G$17)
D6	=ACOS(COS(RADIANS(90-A6)) *COS(RADIANS(90-E6)) +SIN(RADIANS(90-A6)) *SIN(RADIANS(90-E6)) *COS(RADIANS(B6-F6))) *6371

 

[Mdeco]

Thanks, AlanY! That's very helpful.

For my edification, can I ask why you place two hyphens after the opening of the first set of brackets? SUMPRODUCT(--

this will sum up the sales if set 2 co-ords is within 3175 miles (B3) from set 1

	A	B	C	D	E	F	G
1
2
3	Radius	3175
4
5	Lat	Long	Sum Column G if within range B3		Lat 2	Long 2	Sales
6	42.12579	-72.6453	16	2641.506	41.3777	-104.66	1
7	42.18484	-71.9472	16	3113.557	43.9509	-110.553	1
8	42.10273	-72.081	16	3132.926	43.50537	-110.787	2
9	42.4464	-71.4594	12	3171.618	43.50537	-110.787	3
10	42.50484	-71.2015	4	3187.696	43.45279	-110.739	1
11	42.47211	-70.9978	2	3204.765	43.45279	-110.739	1
12	42.537	-70.9736	2	3204.727	43.45279	-110.739	1
13	42.34721	-71.0861	3	2886.476	44.6531	-107.025	1
14	42.20222	-71.0052	2	3243.093	43.04427	-111.009	1
15	42.35756	-71.2116	4	3163.091	42.7857	-110.187	1
16	42.37915	-71.1843	4	3200.111	43.48236	-110.84	1
17	41.82981	-70.1388	2	2641.506	43.48236	-110.84	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C6 =SUMPRODUCT(--(ACOS(COS(RADIANS(90-$A6)) *COS(RADIANS(90-$E$6:$E$17)) +SIN(RADIANS(90-$A6)) *SIN(RADIANS(90-$E$6:$E$17)) *COS(RADIANS($B6-$F$6:$F$17))) *6371<$B$3),$G$6:$G$17) D6 =ACOS(COS(RADIANS(90-A6)) *COS(RADIANS(90-E6)) +SIN(RADIANS(90-A6)) *SIN(RADIANS(90-E6)) *COS(RADIANS(B6-F6))) *6371 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

the first part of the formula compare the distance between the 2 sets of co-ords with the distance in B3 that yields an array of {ture, false, ture,...etc).
the -- is to convert that array to {1, 0, 1,...etc} in order for it to multiple the array in Col G for the summation

 

[Mdeco]

the first part of the formula compare the distance between the 2 sets of co-ords with the distance in B3 that yields an array of {ture, false, ture,...etc).
the -- is to convert that array to {1, 0, 1,...etc} in order for it to multiple the array in Col G for the summation

That's helpful, thank you!

 

[AlanY]

you're welcome