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:

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

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
Radius
Lat	Long	Sum Column G if within range B3	Lat 2	Long 2	Sales

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3175[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]42.12579[/TD]
[TD="align: right"]-72.6453[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]16[/TD]
[TD="align: right"]2641.506[/TD]
[TD="align: right"]41.3777[/TD]
[TD="align: right"]-104.66[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]42.18484[/TD]
[TD="align: right"]-71.9472[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]16[/TD]
[TD="align: right"]3113.557[/TD]
[TD="align: right"]43.9509[/TD]
[TD="align: right"]-110.553[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]42.10273[/TD]
[TD="align: right"]-72.081[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]16[/TD]
[TD="align: right"]3132.926[/TD]
[TD="align: right"]43.50537[/TD]
[TD="align: right"]-110.787[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]42.4464[/TD]
[TD="align: right"]-71.4594[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12[/TD]
[TD="align: right"]3171.618[/TD]
[TD="align: right"]43.50537[/TD]
[TD="align: right"]-110.787[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]42.50484[/TD]
[TD="align: right"]-71.2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="align: right"]3187.696[/TD]
[TD="align: right"]43.45279[/TD]
[TD="align: right"]-110.739[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]42.47211[/TD]
[TD="align: right"]-70.9978[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="align: right"]3204.765[/TD]
[TD="align: right"]43.45279[/TD]
[TD="align: right"]-110.739[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]42.537[/TD]
[TD="align: right"]-70.9736[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="align: right"]3204.727[/TD]
[TD="align: right"]43.45279[/TD]
[TD="align: right"]-110.739[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]42.34721[/TD]
[TD="align: right"]-71.0861[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="align: right"]2886.476[/TD]
[TD="align: right"]44.6531[/TD]
[TD="align: right"]-107.025[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]42.20222[/TD]
[TD="align: right"]-71.0052[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="align: right"]3243.093[/TD]
[TD="align: right"]43.04427[/TD]
[TD="align: right"]-111.009[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]42.35756[/TD]
[TD="align: right"]-71.2116[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="align: right"]3163.091[/TD]
[TD="align: right"]42.7857[/TD]
[TD="align: right"]-110.187[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]42.37915[/TD]
[TD="align: right"]-71.1843[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="align: right"]3200.111[/TD]
[TD="align: right"]43.48236[/TD]
[TD="align: right"]-110.84[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]41.82981[/TD]
[TD="align: right"]-70.1388[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="align: right"]2641.506[/TD]
[TD="align: right"]43.48236[/TD]
[TD="align: right"]-110.84[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=ACOS(COS(RADIANS(90-A6)) *COS(RADIANS(90-E6)) +SIN(RADIANS(90-A6)) *SIN(RADIANS(90-E6)) *COS(RADIANS(B6-F6))) *6371[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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