# Re: Simple Count from Table 1

Solved
Jump to Solution
659 3
cancel
Showing results for
Search instead for
Did you mean:
6 - Interface Innovator

Hi All,
I hope someone could help me a better way of doing this. I have two tables on a base (Table 1 and Table 2).

In Table 1, data is shown below:

Here is what I am trying to do in Table 2:

1. Count all similar State Country entries from table 1
2. Count all EMEA, APAC and AMERICAS from table 1

I tried using the Pivot Table app but the lack of exporting it to CSV is not ideal for me.

1 Solution

Accepted Solutions
16 - Uranus

Convert your `{State / Country}` field into a `link to another record`-type field, connect it to Table 2. This will create one record in Table 2 for each unique `{State / Country}` value. To reduce repetition, you should add a `{Region}` field to Table 2 and make the `{Region}` field in Table 1 a `lookup`-type field.

From there you can add a `count`-type field to Table 2, which will show you the number of records from Table 1 linked to each state/country.

Do a similar process for Region: convert the `{Region}` field in Table 2 (not Table 1) in to a `link to another record`-type field and connect it to another table (Table 3). Then add a `rollup`-type field that uses the `SUM(values)` aggregation to add up the `count` field from Table 2. This will give you the total of all Table1 records linked to a state/country in that region.

6 Replies 6
16 - Uranus

Convert your `{State / Country}` field into a `link to another record`-type field, connect it to Table 2. This will create one record in Table 2 for each unique `{State / Country}` value. To reduce repetition, you should add a `{Region}` field to Table 2 and make the `{Region}` field in Table 1 a `lookup`-type field.

From there you can add a `count`-type field to Table 2, which will show you the number of records from Table 1 linked to each state/country.

Do a similar process for Region: convert the `{Region}` field in Table 2 (not Table 1) in to a `link to another record`-type field and connect it to another table (Table 3). Then add a `rollup`-type field that uses the `SUM(values)` aggregation to add up the `count` field from Table 2. This will give you the total of all Table1 records linked to a state/country in that region.

6 - Interface Innovator

Hi Kamille,

This is very helpful! Thank you! - I cant seem to make the rollup file type work though. I must be doing something wrong.

16 - Uranus

Are you getting an error? can you post a screenshot of what you have thusfar?

6 - Interface Innovator

Hi Kamille,

All I get is 0.

16 - Uranus

Is your Count field in Table 2 working properly? The `{Region Rollup}` field should be doing `SUM(values)` for the Count field, so if the Count fields are showing values greater than 0 this is an issue.

6 - Interface Innovator

I figure it out - thank you for your help!