Count data from one field IF another field in same record says "Bedfordshire"

[Adam_Balcomb]

Hi guys! I’m very new to Airtable and so far I’m loving it! However, I’m having an issue with formula’s. I have a feeling I’m on the right track, just not quite getting it. I have a table called Schools. This table contains all the data relating to each school such as school name, email, phone, pupil total etc.

I have created a field called Beds Total Schools with a formula stating County=“Bedfordshire”. This then populates that field with 1 if the record has Bedfordshire in it’s County field. I can then use this to create a Summary count in my Dashboard called Total Bedfordshire Schools. I can do this for any other county too.

The issue I’m having is as follows. I want to create a field that counts the record’s Pupil Total field IF the County field says “Bedfordshire”. I’m sure it’s something like COUNT {Pupil Total} IF {County}=“Bedfordshire”. I can then create a summary from the new field called something like {Total Beds Pupils}.

Any help would be greatly appreciated!!
Thanks in advance.

Adam

[JonathanBowen · Accepted solution]

Hi @Adam_Balcomb - you’re nearly there - create new field with the formula:

IF({In Bedfordshire?} = 1, {# of pupils}, 0)

You can then roll this up on your dashboard as you have done with the schools count.

However, there is a better way to achieve this by breaking the counties out into their own table. The problem with the current method above is that you would have to create a column for schools and pupils in every other county, which is overkill if you have a lot of counties.

If you have a counties table like this:

Then you can link to this in the schools table:

Back in the counties table you can rollup both schools and counties to give totals (COUNTALL for schools, SUM for pupils) and no additional fields are required.

JB

[Adam_Balcomb]

Hi Jonathan, thank you so much for that! I like the Rollup idea better like you say it streamlines things so the Tables don’t get cluttered. I have created a new Table called Counties and entered the names of the counties into the Name fields. I’ve then linked the County field in my Schools table back to this one and it works great.

I’ve created the Total Schools column in my Counties table and created a Rollup.

In the Rollup settings I’ve chosen the following:

Field on this table that links to the records you want to summarize - Schools
Schools table field that you’d like to roll up - County
Enter an aggregation function which rolls up the values in each linked record - COUNTALL(Schools)

When I view the Counties table now, the Total Schools field says only 1 for each county even if a county hasn’t even been added to the schools table yet.

Regards
Adam

[Adam_Balcomb]

Ah! I’ve changed Total Schools from a rollup to Count and it’s now working perfectly!