Count number of occurrences based on range

[Joe_Ross]

I need help with a formula in a market segmentation table. In this document, one table “Segmentation” contains the definition of a few market segments. The other table “Market Data” contains population data for a list of cities.

I am looking to count the number of cities that have a population within a range as defined in the segmentation table. The population range is defined with a Population (min) column for the low end of the range and a Population (max) column for the upper end of the range.

I need help with a formula in the “Count” column that return the expected result.

[Joe_Ross]

I included an “expected result” column to represent what I want the calculated “Count” column to return based on the current data in the Market Data Table.

[Christiaan_Huizer]

hi @Joe_Ross , I can understand why this feels difficult, however the solution is simpler than you may have expected and easier than in any spreadsheet:

next we add the count and this is the function you need (but you can polish by adding the is equal symbol as logical operator may that fit your needs).

[Market Data].Filter(
  Population > thisRow.[Population (min)] and 
  Population < thisRow.[Population (Max)]).Count()

Cheers, Christiaan

[Joe_Ross]

That worked great. Thank you very much for the support and opportunity to learn.