Sumifs - looking up values within a certain distance of a point

[Stirling_confused]

I have two sets of data – list A is a list of train stations, with their longitude and latitude. List B is a list of places, with the population and a longitude/latitude. I want to be able to write a formula that takes a particular station, and sums the population in List B within 25miles.



List A:

Station	Longitude	Lattitude	Population within 25 miles
Station A	51.49106​	0.121394​
Station B	51.57496​	-3.22983​
Station C	51.64471​	-3.32699​
Station D	51.71506​	-3.44308​
Station E	57.1437​	-2.09869​
Station F	56.05459​	-3.30056​
Station G	52.54396​	-4.05707​
Station H	52.89858​	-4.37418​
Station I	51.81669​	-3.00965​
Station J	53.29457​	-3.58262​
Station K	52.41404​	-4.08189​
Station L	53.75297​	-2.36955​

List B:

Description	Lattitude	Longitude	Population
Place A	51.519589​	-0.096277417​	1475​
Place B	50.45758​	-4.7809718​	1359​
Place C	51.632212​	-0.14008798​	1495​
Place D	50.855827​	-0.77556035​	1524​
Place E	50.873154​	0.60325584​	1819​
Place F	51.294288​	-0.74939155​	2342​
Place G	52.485147​	-1.7922926​	1617​
Place H	51.510605​	-0.21576033​	1594​
Place I	51.489449​	-0.14014192​	1362​
Place J	51.144167​	0.90731201​	2253​
Place K	51.389227​	-0.42091316​	1635​
Place L	51.506754​	-0.025943412​	1591​
Place M	51.067315​	-0.33381087​	1782​

I know how to calculate the distance between two points using an ACOS formula, however am a bit lost as to how to do a sumifs that calculates the distance between two points. Any help much appreciated

 

[JamesCanale]

I'll take your word on it for the ACOS formula for distance between lats and longs. You can put it in here (I use simple pythogarus).

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	Station	Longitude	Lattitude	Population within 25 miles	Distance	4		Description	Lattitude	Longitude	Population
3	Station A	51.49106	0.121394	20489				Place A	51.519589	-0.096277417	1475
4	Station B	51.57496	-3.22983	19595				Place B	50.45758	-4.7809718	1359
5	Station C	51.64471	-3.32699	17776				Place C	51.632212	-0.14008798	1495
6	Station D	51.71506	-3.44308	17776				Place D	50.855827	-0.77556035	1524
7	Station E	57.1437	-2.09869	0				Place E	50.873154	0.60325584	1819
8	Station F	56.05459	-3.30056	1617				Place F	51.294288	-0.74939155	2342
9	Station G	52.54396	-4.05707	10071				Place G	52.485147	-1.7922926	1617
10	Station H	52.89858	-4.37418	5318				Place H	51.510605	-0.21576033	1594
11	Station I	51.81669	-3.00965	21848				Place I	51.489449	-0.14014192	1362
12	Station J	53.29457	-3.58262	17776				Place J	51.144168	0.907310201	2253
13	Station K	52.41404	-4.08189	11853				Place K	51.389227	-0.42091316	1635
14	Station L	53.75297	-2.36955	16417				Place L	51.506754	-0.025943412	1591
15								Place M	51.067315	-0.33381087	1782
Sheet8

Cell Formulas
Range	Formula
D3:D14	D3	=SUMPRODUCT(--(SQRT(($I$3:$I$15-B3)^2+($J$3:$J$15-C3)^2)<$F$2),$K$3:$K$15)

Replace:
SQRT(($I$3:$I$15-B3)^2+($J$3:$J$15-C3)^2)
with what you know.

 

[Eric W]

I wrote up a UDF to calculate the distance, and then I had to adapt it to handle arrays. Here it is:

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Variant, long2 As Variant, Optional km As Long = 0)
Dim d1 As Object, d2 As Object, i As Long, x As Variant, lat As Variant, lng As Variant

    Set d1 = CreateObject("Scripting.Dictionary")
    Set d2 = CreateObject("Scripting.Dictionary")
    
    If TypeOf lat2 Is Range Then
        For Each x In lat2.Cells
            d1.Add x, 0
        Next x
        For Each x In long2.Cells
            d2.Add x, 0
        Next x
    ElseIf IsArray(lat2) Then
        For Each x In lat2
            d1.Add x, 0
        Next x
        For Each x In long2
            d2.Add x, j0
        Next x
    Else
        d1.Add lat2, 0
        d2.Add long2, 0
    End If
    
    lat = d1.keys
    lng = d2.keys
    
    For i = 0 To UBound(lat)
        
        a = Evaluate("COS(RADIANS(" & lat(i) - lat1 & "))/2")
        b = Evaluate("COS(RADIANS(" & lat1 & "))*COS(RADIANS(" & lat(i) & "))")
        c = Evaluate("(1-COS(RADIANS(" & lng(i) - long1 & ")))/2")
        d = 12742 * Evaluate("ASIN(SQRT(" & 0.5 - a + b * c & "))")
    
        If km = 1 Then d = d * 0.6213
        lat(i) = d
    Next i
        
    Haversine = WorksheetFunction.Transpose(lat)
    
End Function

Once that was complete, you can use it in essentially the same formula that James provided:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Station	Longitude	Lattitude	Population within 25 miles		Description	Lattitude	Longitude	Population		Distance
2	Station A	51.49106	0.121394	9152		Place A	51.51959	-0.09627742	1475		25
3	Station B	51.57496	-3.22983	0		Place B	50.45758	-4.7809718	1359
4	Station C	51.64471	-3.32699	0		Place C	51.63221	-0.14008798	1495
5	Station D	51.71506	-3.44308	0		Place D	50.85583	-0.77556035	1524
6	Station E	57.1437	-2.09869	0		Place E	50.87315	0.60325584	1819
7	Station F	56.05459	-3.30056	0		Place F	51.29429	-0.74939155	2342
8	Station G	52.54396	-4.05707	0		Place G	52.48515	-1.7922926	1617
9	Station H	52.89858	-4.37418	0		Place H	51.51061	-0.21576033	1594
10	Station I	51.81669	-3.00965	0		Place I	51.48945	-0.14014192	1362
11	Station J	53.29457	-3.58262	0		Place J	51.14417	0.90731201	2253
12	Station K	52.41404	-4.08189	0		Place K	51.38923	-0.42091316	1635
13	Station L	53.75297	-2.36955	0		Place L	51.50675	-0.02594341	1591
14						Place M	51.06732	-0.33381087	1782
Sheet2

Cell Formulas
Range	Formula
D1	D1	="Population within "&K2&" miles"
D2:D13	D2	=SUMPRODUCT($I$2:$I$14,--(haversine(B2,C2,$G$2:$G$14,$H$2:$H$14,1)<=$K$2))

I was a little surprised to see that most of the distances were over 25 miles, so were not included in the totals. I spot checked some of the results of my UDF with some online calculators, and they match. There could still be a problem with my UDF, but check it out and see what you think.

 

[Stirling_confused]

I'll take your word on it for the ACOS formula for distance between lats and longs. You can put it in here (I use simple pythogarus).

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	Station	Longitude	Lattitude	Population within 25 miles	Distance	4		Description	Lattitude	Longitude	Population
3	Station A	51.49106	0.121394	20489				Place A	51.519589	-0.096277417	1475
4	Station B	51.57496	-3.22983	19595				Place B	50.45758	-4.7809718	1359
5	Station C	51.64471	-3.32699	17776				Place C	51.632212	-0.14008798	1495
6	Station D	51.71506	-3.44308	17776				Place D	50.855827	-0.77556035	1524
7	Station E	57.1437	-2.09869	0				Place E	50.873154	0.60325584	1819
8	Station F	56.05459	-3.30056	1617				Place F	51.294288	-0.74939155	2342
9	Station G	52.54396	-4.05707	10071				Place G	52.485147	-1.7922926	1617
10	Station H	52.89858	-4.37418	5318				Place H	51.510605	-0.21576033	1594
11	Station I	51.81669	-3.00965	21848				Place I	51.489449	-0.14014192	1362
12	Station J	53.29457	-3.58262	17776				Place J	51.144168	0.907310201	2253
13	Station K	52.41404	-4.08189	11853				Place K	51.389227	-0.42091316	1635
14	Station L	53.75297	-2.36955	16417				Place L	51.506754	-0.025943412	1591
15								Place M	51.067315	-0.33381087	1782
Sheet8

Cell Formulas
Range	Formula
D3:D14	D3	=SUMPRODUCT(--(SQRT(($I$3:$I$15-B3)^2+($J$3:$J$15-C3)^2)<$F$2),$K$3:$K$15)

Replace:
SQRT(($I$3:$I$15-B3)^2+($J$3:$J$15-C3)^2)
with what you know.

Hi

Thank you very much both for replying. And apologies for the delay in responding.

For some reason i can't quite get this to work with my formula: =ACOS( SIN(lat1)*SIN(lat2) + COS(lat1)*COS(lat2)*COS(lon2-lon1) ) * 3959

Is this an inappropriate formula to use with an array?

 

[JamesCanale]

The formula works with arrays. But here is the output I'm getting comparing the first Station with all of the Places: I don't know what units these are supposed to be. They don't seem to coorespond to something l understand.

5456.982​

1117.086​

5584.376​

7328.369​

2971.508​

6605.898​

3194.528​

5599.724​

5491.8​

3177.236​

5914.655​

5359.269​

5842.516​