Sumif within distance from a latitude and longitude pairing

Mdeco

New Member
Joined
Nov 19, 2018
Messages
3
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:

1ABCDEFG
2
3Radius100
4
5LatLongSum Column G if within range B3Lat 2Long 2Sales
642.125793-72.645334?41.377698-104.6603531
742.184835-71.947184?43.950896-110.5531811
842.10273-72.080996?43.50537-110.7865272
942.446396-71.459405?43.50537-110.7865273
1042.504844-71.201539?43.452793-110.7392661
1142.472112-70.997794?43.452793-110.7392661
1242.536996-70.973646?43.452793-110.7392661
1342.347207-71.086095?44.653104-107.0247131
1442.202216-71.005192?43.044268-111.0085171
1542.357564-71.211649?42.785695-110.1865991
1642.379146-71.184299?43.482361-110.8397821
1741.829813-70.138834?43.482361-110.8397822

<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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
this will sum up the sales if set 2 co-ords is within 3175 miles (B3) from set 1

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Radius</td><td style="text-align: right;background-color: #E2EFDA;;">3175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Lat</td><td style=";">Long</td><td style=";">Sum Column G if within range B3</td><td style="text-align: right;;"></td><td style=";">Lat 2</td><td style=";">Long 2</td><td style=";">Sales</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">42.12579</td><td style="text-align: right;;">-72.6453</td><td style="text-align: right;background-color: #E2EFDA;;">16</td><td style="text-align: right;;">2641.506</td><td style="text-align: right;;">41.3777</td><td style="text-align: right;;">-104.66</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">42.18484</td><td style="text-align: right;;">-71.9472</td><td style="text-align: right;background-color: #E2EFDA;;">16</td><td style="text-align: right;;">3113.557</td><td style="text-align: right;;">43.9509</td><td style="text-align: right;;">-110.553</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">42.10273</td><td style="text-align: right;;">-72.081</td><td style="text-align: right;background-color: #E2EFDA;;">16</td><td style="text-align: right;;">3132.926</td><td style="text-align: right;;">43.50537</td><td style="text-align: right;;">-110.787</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">42.4464</td><td style="text-align: right;;">-71.4594</td><td style="text-align: right;background-color: #E2EFDA;;">12</td><td style="text-align: right;;">3171.618</td><td style="text-align: right;;">43.50537</td><td style="text-align: right;;">-110.787</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">42.50484</td><td style="text-align: right;;">-71.2015</td><td style="text-align: right;background-color: #E2EFDA;;">4</td><td style="text-align: right;;">3187.696</td><td style="text-align: right;;">43.45279</td><td style="text-align: right;;">-110.739</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">42.47211</td><td style="text-align: right;;">-70.9978</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;;">3204.765</td><td style="text-align: right;;">43.45279</td><td style="text-align: right;;">-110.739</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">42.537</td><td style="text-align: right;;">-70.9736</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;;">3204.727</td><td style="text-align: right;;">43.45279</td><td style="text-align: right;;">-110.739</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">42.34721</td><td style="text-align: right;;">-71.0861</td><td style="text-align: right;background-color: #E2EFDA;;">3</td><td style="text-align: right;;">2886.476</td><td style="text-align: right;;">44.6531</td><td style="text-align: right;;">-107.025</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">42.20222</td><td style="text-align: right;;">-71.0052</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;;">3243.093</td><td style="text-align: right;;">43.04427</td><td style="text-align: right;;">-111.009</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">42.35756</td><td style="text-align: right;;">-71.2116</td><td style="text-align: right;background-color: #E2EFDA;;">4</td><td style="text-align: right;;">3163.091</td><td style="text-align: right;;">42.7857</td><td style="text-align: right;;">-110.187</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">42.37915</td><td style="text-align: right;;">-71.1843</td><td style="text-align: right;background-color: #E2EFDA;;">4</td><td style="text-align: right;;">3200.111</td><td style="text-align: right;;">43.48236</td><td style="text-align: right;;">-110.84</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">41.82981</td><td style="text-align: right;;">-70.1388</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;;">2641.506</td><td style="text-align: right;;">43.48236</td><td style="text-align: right;;">-110.84</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">ACOS(<font color="Green">COS(<font color="Purple">RADIANS(<font color="Teal">90-$A6</font>)</font>) *COS(<font color="Purple">RADIANS(<font color="Teal">90-$E$6:$E$17</font>)</font>) +SIN(<font color="Purple">RADIANS(<font color="Teal">90-$A6</font>)</font>) *SIN(<font color="Purple">RADIANS(<font color="Teal">90-$E$6:$E$17</font>)</font>) *COS(<font color="Purple">RADIANS(<font color="Teal">$B6-$F$6:$F$17</font>)</font>)</font>) *6371<$B$3</font>),$G$6:$G$17</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=ACOS(<font color="Blue">COS(<font color="Red">RADIANS(<font color="Green">90-A6</font>)</font>) *COS(<font color="Red">RADIANS(<font color="Green">90-E6</font>)</font>) +SIN(<font color="Red">RADIANS(<font color="Green">90-A6</font>)</font>) *SIN(<font color="Red">RADIANS(<font color="Green">90-E6</font>)</font>) *COS(<font color="Red">RADIANS(<font color="Green">B6-F6</font>)</font>)</font>) *6371</td></tr></tbody></table></td></tr></table><br />
 

Mdeco

New Member
Joined
Nov 19, 2018
Messages
3
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

ABCDEFG
1
2
3Radius3175
4
5LatLongSum Column G if within range B3Lat 2Long 2Sales
642.12579-72.6453162641.50641.3777-104.661
742.18484-71.9472163113.55743.9509-110.5531
842.10273-72.081163132.92643.50537-110.7872
942.4464-71.4594123171.61843.50537-110.7873
1042.50484-71.201543187.69643.45279-110.7391
1142.47211-70.997823204.76543.45279-110.7391
1242.537-70.973623204.72743.45279-110.7391
1342.34721-71.086132886.47644.6531-107.0251
1442.20222-71.005223243.09343.04427-111.0091
1542.35756-71.211643163.09142.7857-110.1871
1642.37915-71.184343200.11143.48236-110.841
1741.82981-70.138822641.50643.48236-110.842

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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

New Member
Joined
Nov 19, 2018
Messages
3
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,099,253
Messages
5,467,563
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top