Formula to count records in linked tables based on values

[offps]

Airtable newbie here, if this question has a simple answer that I should be aware of please accept my apology and direct me to the right resource

I’m trying a simple operation that I feel should be possible. How do I count number of records in multiple linked tables based on input values?

I have a table called Projects. Projects can have a status based on a value from another linked table called Status (currently three different values are available in Status: planned, started, finished). Project are also assigned a region from another linked table called Region (currently: West, Central and East)

I want, in a fourth table called Results, count the number of projects with a certain “status” AND “region” combination. Is this possible? I’m foreseing that I have Region and Status as columns in Result and then countif in Projects based on the value in those two coulms.

Thanks a million

[Kamille_Parks]

For this to work in a [Results] table, you would have to make sure every project is linked to a record in that table. Then you could use a Count field and use the “Only include linked records from the Projects table that meet certain conditions” option.

Alternatively, you could just add the Count field to the [Regions] table since it sounds like you’ve already linked regions to projects.

[offps]

Thank you Kamille! I’m not quite following your suggestion, sorry :slightly_smiling_face:

I’ve created a dummy base with some data to better illustrate https://airtable.com/invite/l?inviteId=invfr6XKdhd4LizGS&inviteToken=3f4e3cb95287103b0f24257e2732d31...

I understand that I could link each project to a result and sum up the count but then I would have to update both the status of the project and the result of the project everytime there is a change. Does not seem like the best solution.

Is there really no way in Airtable to do a count with a variable condition per row?

[Kamille_Parks]

I edited your base to follow my second suggestion (using a conditional count field in the “Regions” table). I didn’t make any changes to the Results table because I’m not sure you need one if all you wanted to do was count records.

[offps]

Great Kamille and I’m so grateful for your effort. This works well for this base but doesn’t scale so well. As I’m starting to add more dimensions (team member, industry, intiative, products, etc). But let me think, maybe I need to export the database and do this kind of reporting externally in a visualization tool

[Kamille_Parks]

If you have a pro account I’d recommend using the Pivot Table block then