To count locations within a distance in Google Sheets, geocode your addresses with =GEOCODE(A2:A200), then run =WITHIN_RADIUS(B2, C2, B2:B200, C2:C200, 5). Wrap the output range in =COUNTA() to return the exact total number of properties inside that specific radius.
This setup is built for real estate agents managing a 200-home neighbourhood farm, logistics coordinators assigning loads to 5 local crews, or sales teams routing 47 daily stops. You get a hard count of nearby addresses and a live visual map without manually measuring distances or exporting your data.
- →Combine =COUNTA() with =WITHIN_RADIUS() to tally addresses inside a specific distance from a target point.
- →You must process coordinates through =GEOCODE(A2:A500) before calculating the radius.
- →Use =INSTAMAP() to generate a live, shareable URL visualising your counted locations.
- →The free tier provides 100 lookups per day, which increases to 1,000 with a free email unlock.
- →The InstaMaps sidebar (Extensions > InstaMaps > Enable formulas) writes these chains automatically via the Build-the-workflow button.
- →Best suited for circle prospecting and neighbourhood canvassing under 50,000 rows.
The =WITHIN_RADIUS and =COUNTA recipe
To calculate the tally dynamically without dragging formulas down the column, wrap the radius output in an array. Enter the following in your summary cell: =COUNTA(FILTER(C2:C200, WITHIN_RADIUS(B2:B200, F1, 2))). Using FILTER alongside COUNTA ignores FALSE results and counts only the coordinates that register as TRUE.
Alternatively, if you want to avoid helper columns entirely, embed the radius check directly into your count: =ARRAYFORMULA(COUNTIF(WITHIN_RADIUS(B2:B200, F1, 2), TRUE)). This processes the entire 199-row range in a single cell, evaluating the distance constraints in memory before returning the integer.
If the geocoding array in column B fails to return a coordinate for a malformed address (returning an #ERROR!), the WITHIN_RADIUS function will also throw an error, breaking the COUNTIF chain. Ensure you clean addresses using =CLEAN_ADDRESS(A2:A200) before geocoding to prevent null coordinate errors.
Geocode the dataset. Enter =GEOCODE(A2:A200) into cell B2. This returns latitude and longitude pairs for column B. The InstaMaps free tier handles 100 lookups daily, or 1,000 daily with a free email unlock.
Set the origin point. Input your target coordinate into a fixed cell, such as E1. If E1 contains a postcode string instead of a coordinate, geocode it in F1 using =GEOCODE(E1) to avoid #VALUE! errors.
Run the radius check. In C2, enter =WITHIN_RADIUS(B2, F$1, 2). This evaluates if the property in B2 sits within a 2-mile radius of your origin point, returning a boolean TRUE or FALSE. Drag this formula down to C200.
Count the matches. In cell D1, input =COUNTIF(C2:C200, TRUE) to generate the final numeric tally of properties inside the radius.
Worked example: Circle prospecting a 200-home farm
Consider an agent prospecting a 200-property farm around a target postcode in central Birmingham. Column A (A2:A201) contains the 200 raw addresses of the farm. In cell B2, the formula =GEOCODE(A2:A201) processes the list. The add-on's free tier provides 1,000 lookups per day with the email unlock, meaning this 200-row operation consumes just 20% of the daily capacity, leaving 800 lookups for other neighbourhoods.
The agent identifies their target property-a recent listing-and geocodes it in cell E1. They set their circle prospecting radius to 0.75 miles in cell F1. To get the immediate count of homes within the target zone, they use =ARRAYFORMULA(COUNTIF(WITHIN_RADIUS(B2:B201, E1, F1), TRUE)). The formula evaluates the 200 coordinates against the origin point. For this dataset, it returns 52.
To view the specific 52 properties, the agent runs =WITHIN_RADIUS(B2:B201, E1, F1) down column C. Wrapping the output in =FILTER(A2:A201, C2:C201=TRUE) isolates those 52 addresses into a contiguous list.
With 52 stops identified, the agent needs a physical route. They pass these addresses into =SORT_BY_DISTANCE(), ordered from the origin point. To push this route into a navigation app, they use =ROUTE_LINK(). Because =ROUTE_LINK() strictly relies on Google Maps' official URL scheme, the function caps at a maximum of 11 stops. The agent must split the 52 addresses into five separate lists of 10 or 11 addresses to generate valid navigation links.
If A85 contains an improperly formatted postcode, =GEOCODE() returns an #N/A in B85. Consequently, the WITHIN_RADIUS function evaluates B85 as an error, safely ignoring it in the COUNTIF array without breaking the total 52-home tally. To fix the gap, the agent can apply =CLEAN_ADDRESS(A85) to standardise the formatting and re-run the geocode.
Finally, they verify driving constraints using =TRAVEL_TIME() between the batches. By checking the travel times between the furthest properties in the batch, the agent ensures the 52-stop route fits within a standard four-hour shift without running out of daylight.
Mapping the counted radius results
Once you isolate the addresses within your radius, pass them into =INSTAMAP() to generate a live, hosted shareable map URL. Assuming your filtered addresses sit in column D (D2:D53), input =INSTAMAP(D2:D53) into cell E1. The function outputs a secure URL. Clicking this link opens a browser tab displaying a map of the 52 properties, complete with coordinate pins.
This map is dynamic. Because the INSTAMAP function references the cell range rather than static values, the hosted URL updates automatically when the sheet changes. If you add a new property to row 54 that falls inside the WITHIN_RADIUS parameters, the map adds the pin to the hosted URL without requiring you to regenerate the link. Anyone viewing the shared URL sees the updated pins in real-time.
The INSTAMAP function accepts raw coordinates directly. If you prefer to map the boolean results of your radius check, ensure your INSTAMAP range points directly to the coordinate column (e.g., column B) rather than the boolean column. Plotting text values returns an empty map layer.
To build this without typing formulas, use the InstaMaps sidebar. Open it via Extensions > InstaMaps > Enable formulas. Select your target range and use the Build-the-workflow button. The tool automatically writes the geocoding, radius, and INSTAMAP formula chains based on your selected columns.
If your address list exceeds 1,000 rows and you hit the daily geocoding limit, the INSTAMAP function will still plot whatever coordinates successfully processed in column B. You can return the next day to finish the remaining rows, and the map will automatically ingest the new data upon sheet refresh.
Handling edge cases and exact errors
When =WITHIN_RADIUS() evaluates a target coordinate against an array, it expects pure latitude and longitude decimal values (e.g., 51.5074, -0.1278). If your target geocoded cell (e.g., B2) is empty because the address failed to parse, =COUNTA(WITHIN_RADIUS(C2:C500, B2, 5)) immediately throws a #VALUE! error. The exact same failure occurs if the radius array contains raw text strings instead of geographic coordinates.
To prevent processing failures, run raw location data through =CLEAN_ADDRESS() before attempting any radius counts. If you have messy prospect data in column A, insert =CLEAN_ADDRESS(A2) in cell B2 and drag it down to B500. This standardises the address formatting, corrects malformed postcode suffixes, and strips out extraneous unit numbers that cause geocoders to fail.
Once the text is standardised, feed those specific cells into the geocoder: =GEOCODE(B2:B500). If your geocoded range still contains occasional gaps due to unrecognised new-build estates, wrap your count formula in an error catch. Change your recipe to =COUNTA(IFERROR(WITHIN_RADIUS(D2:D500, Target!B2, 5))). Using IFERROR forces the function to ignore the single broken row 412 and process the remaining 498 rows, giving you an accurate count of locations within the specified distance without halting the entire calculation.
Remember that =WITHIN_RADIUS() counts straight-line mathematical matches. If two coordinates are mathematically within your 5-mile radius but separated by an unbridged river, the formula still counts them. Filter out geographically close but practically inaccessible locations by verifying the top results with =TRAVEL_TIME().
Limits and honest alternatives
Google Sheets processes array formulas like =WITHIN_RADIUS() efficiently for standard datasets, but the platform hits hard calculation limits when your prospect farm exceeds 50,000 rows. If you attempt to run =COUNTA(WITHIN_RADIUS(C2:C50000, B2, 5)) on a single tab, your browser tab will freeze and crash. The calculation overhead of computing 50,000 individual trigonometric distance measurements against a single target coordinate overwhelms the sheet's engine.
You must also account for the add-on's daily quota constraints. The InstaMaps free tier provides 100 lookups per day (1,000/day with a free email unlock). If you import a 60,000-row CRM export into column A, you cannot geocode all those addresses in one session. You will hit the quota ceiling at row 1,001, returning blank coordinate cells for the remaining 58,999 rows and breaking your downstream =COUNTA() formula.
Instead of running one massive array, partition your data into smaller batches across separate 5,000-row tabs (e.g., North, South, East, West). Calculate the radius counts locally on each specific tab, then sum the four individual =COUNTA() results on a master dashboard. For example, use =North!D1 + South!D1 + East!D1 + West!D1 to seamlessly aggregate the localised counts without stalling the spreadsheet.
To avoid manual syntax errors during this batching process, use the add-on interface. Navigate to Extensions > InstaMaps > Enable formulas. Highlight your target columns and click the Build-the-workflow button. The tool automatically writes the chained formulas-wrapping your =CLEAN_ADDRESS(), =GEOCODE(), and =WITHIN_RADIUS() functions in the correct sequence-so you do not have to hand-type complex array references.
For handling larger regional datasets without building from scratch, download the pre-configured batch processing structures at get-instamaps.com/templates. These templates manage the row-limit splitting automatically.
Who this specific setup is for
This exact recipe is designed for users managing localised datasets who need immediate, automated geographic counts without learning a dedicated mapping software.
**Field sales teams:** Real estate agents and insurance brokers operating defined geographic territories. If you manage a 200-home farm and need to know exactly how many past clients sit within a 5-mile radius of an active property listing, this recipe counts them instantly. It tells you if a circle prospecting campaign is viable before you print mailers.
**Local service businesses:** Plumbing, HVAC, and landscaping companies scheduling daily dispatch. You can calculate how many existing maintenance contracts fall inside a 10-mile radius of a new emergency repair call to cluster technician visits and minimise fuel costs.
**Delivery route planners:** Local courier services determining drop density. If you have 47 stops in a specific spreadsheet, checking how many sit within a tight 2-mile radius helps you build dense, efficient walking routes.
**Who should skip this setup:** Enterprise logistics managers running cross-country freight networks. If your dataset routinely exceeds 50,000 rows and spans multiple time zones, Google Sheets lacks the required spatial database architecture. Move that data into a dedicated GIS platform. This =WITHIN_RADIUS() workflow is built for frontline workers analysing neighbourhood data, not continental supply chain modelling.
Map your Salesforce accounts in under 5 minutes — no admin setup.
Common Questions
First, geocode your target addresses by placing =GEOCODE(A2:A200) in column B to extract latitude and longitude. Next, use =WITHIN_RADIUS(B2, C2, B2:B200, C2:C200, 10) to check which points fall within 10 miles of your origin coordinate. Finally, wrap the output in =COUNTA() or apply =COUNTIF(range, TRUE) against the boolean results to tally the exact number of matching locations.
No, the formula requires numerical latitude and longitude coordinates to measure the circle accurately. You must run your text addresses through =CLEAN_ADDRESS() and then =GEOCODE() to generate the required decimal degrees. If you attempt to pass raw text like "123 Main St" into the coordinate parameters, the add-on will return a #VALUE! error.
Once your radius formula isolates the nearby locations, you can generate a visual output using =INSTAMAP(). Select the specific rows or columns containing your filtered subset of addresses and apply the function. This produces a live, hosted shareable map URL that updates automatically whenever you add new addresses or alter the radius parameters.
The base free tier allows for 100 lookups per day, which covers the conversion of raw text addresses into coordinates. You can increase this capacity to 1,000 lookups per day with a free email unlock directly in the add-on. The mathematical calculations performed by =WITHIN_RADIUS() do not consume your daily quota, but the initial =GEOCODE() operations do.
A #VALUE! error typically means the formula received text instead of numbers, or you left a blank cell in the middle of your coordinate ranges. Check that your origin coordinates in cells like B2 and C2 contain actual decimal degrees rather than raw text strings. You should also wrap your =GEOCODE() function in =IFERROR() to ensure empty rows in your 200-home farm do not break the radius calculations.
Yes, the hosted URL generated by =INSTAMAP() refreshes whenever the underlying spreadsheet data changes. If you add 47 new stops to your sheet and geocode them, they will immediately populate on the shared map link. The sidebar includes a Build-the-workflow button that inserts these connected chains without manual typing.
Install InstaMaps to geocode your addresses and calculate radius proximity using simple spreadsheet formulas.
Install InstaMaps free