Create a new column

[Jeff2Jets]

I have two tables. For this question, I will call them 'States' and 'Counties'. The 'States' table has many columns about each State; State Capital, State Flag, State Bird, State Flower, etc.

The 'States' table has a column with the name 'State'. There are 50 rows in the table, each with the name of one of the 50 US States, as seen below:

"Alabama"
"Alaska"
...

The 'Counties' table has two columns; 'State' and 'County'. Each row contains the name of a State and a County. For instance,  Alabama has 67 Counties, so there are 67 rows in the County table, as seen below:

"Alabama","Autauga"
"Alabama","Baldwin"
...

I want to create a new numeric column in the 'States' table that shows the # of Counties. I tried using LOOKUP, but that returned multiple values and produced an error. What function can I use to create a numeric column within the 'States' table that contains the # of Counties for each State?

Thanks!

[Jeff2Jets]

Thanks! That was exactly what I needed.

[FreemanZ · Accepted solution]

hi @Jeff2Jets ,

ensure relate both tables, then write a calculated column in states table like:

Count =

COUNTROWS(RELATEDTABLE(Counties))