‹Blog

Airtable Rollup Fields: Count Entries in Lookup Fields (Complete Reference)

FE
Filla Editorial•intermediate•Nov 22, 2025

Count entries in lookup fields with rollups

Rollup fields let you aggregate data from linked records. When you need to count entries in a lookup field, use rollup fields with count functions.

What is a rollup field?

A rollup field performs calculations on values from linked records. It aggregates data from one table into another through linked record relationships.

Example:

  • You have a "Projects" table and a "Tasks" table
  • Tasks are linked to Projects
  • A rollup field in Projects can count how many tasks are linked to each project

Counting entries in lookup fields

COUNTALL: Count all linked records

Use when: You want to count the total number of linked records, regardless of whether fields have values.

COUNTALL(values)

How it works:

  • Counts all linked records
  • Works with any field from the linked table (result is the same)
  • Includes empty/blank records in the count
  • This is the most common function for counting lookup entries

Example: Count all tasks linked to a project:

COUNTALL(values)

Result: If 5 tasks are linked, returns 5 even if some task fields are empty.

COUNT: Count non-empty numeric values

Use when: You want to count only records with numeric values in a specific field.

COUNT(values)

How it works:

  • Counts only non-empty numeric values
  • Ignores text values and empty cells
  • Use when you need to count records with actual numeric data

Example: Count tasks with a "Hours" value:

COUNT(values)

Result: If 5 tasks are linked but only 3 have hours entered, returns 3.

COUNTA: Count non-empty values (any type)

Use when: You want to count records with any non-empty value (text or number).

COUNTA(values)

How it works:

  • Counts non-empty values of any type (text, number, date, etc.)
  • Ignores only completely blank cells
  • More inclusive than COUNT (which only counts numbers)

Example: Count tasks with any description:

COUNTA(values)

Result: If 5 tasks are linked and 4 have descriptions, returns 4.

Comparison: COUNT vs COUNTALL vs COUNTA

Function What it counts Includes empty? Use case
COUNTALL All linked records Yes Total number of linked records
COUNT Non-empty numeric values only No Records with numeric data
COUNTA Non-empty values (text, number, etc.) No Records with any data

Most common: Use COUNTALL(values) when you just need to count how many records are linked.

Setting up a rollup field to count

Step 1: Add a rollup field

  1. In the table where you want the count (e.g., "Projects")
  2. Click "+" to add a new field
  3. Select "Rollup" field type

Step 2: Configure the rollup

  1. Select rollup source: Choose the table with linked records (e.g., "Tasks")
  2. Select field to roll up: Choose any field from the linked table (for counting, it doesn't matter which field)
  3. Aggregation formula: Enter COUNTALL(values)

Step 3: Create the field

Click "Create field" to finish.

Common counting scenarios

Count linked records

Scenario: Count how many tasks are linked to each project.

Rollup formula:

COUNTALL(values)

Result: Returns the total number of linked task records.

Count records with specific values

Scenario: Count tasks with status "Complete".

Rollup formula:

COUNTALL(values)

Configuration:

  • Add a condition: "Only include linked records where Status = Complete"
  • Then use COUNTALL(values) to count only those filtered records

Result: Counts only tasks that meet the condition.

Count records with numeric data

Scenario: Count tasks that have hours logged.

Rollup formula:

COUNT(values)

Result: Returns count of tasks with non-empty numeric values in the hours field.

Count records with any data

Scenario: Count tasks that have any description entered.

Rollup formula:

COUNTA(values)

Result: Returns count of tasks with non-empty description fields.

Complete rollup function reference

Beyond counting, rollup fields support many aggregation functions:

Math functions

SUM: Sum numeric values

SUM(values)

AVERAGE: Calculate mean average

AVERAGE(values)

MAX: Find largest value

MAX(values)

MIN: Find smallest value

MIN(values)

Text functions

CONCATENATE: Join text values together

CONCATENATE(values)

ARRAYJOIN: Join values with a separator

ARRAYJOIN(values, "; ")

Result: "Task 1; Task 2; Task 3"

Array functions

ARRAYCOMPACT: Remove empty strings and null values

ARRAYCOMPACT(values)

ARRAYFLATTEN: Flatten nested arrays

ARRAYFLATTEN(values)

Useful for lookup of lookup scenarios.

ARRAYSLICE: Get subset of array

ARRAYSLICE(values, 1, 3)

Get first 3 items: ARRAYSLICE(values, 1, 3) Get last item: ARRAYSLICE(values, -1)

ARRAYUNIQUE: Return only unique items

ARRAYUNIQUE(values)

Logic functions

AND: Returns true if all values are true

AND(values > 100)

Note: Empty arrays are treated as true (see troubleshooting below).

OR: Returns true if any value is true

OR(values = "Complete")

XOR: Returns true if odd number of values are true

XOR(values)

Conditional rollups (filtering)

You can limit which linked records are included in the rollup:

Add conditions

  1. In rollup field configuration
  2. Check "Only include linked records that meet certain conditions"
  3. Set your filter conditions
  4. The rollup will only aggregate matching records

Example: Count only completed tasks:

  • Condition: Status = "Complete"
  • Formula: COUNTALL(values)
  • Result: Counts only tasks with "Complete" status

Troubleshooting

Empty rollups return true in AND()

Problem: AND({Rollup Field}) unexpectedly returns true even when the rollup is empty.

Why: Empty rollup arrays are treated as "true" by AND().

Fix: Convert to string first:

AND({Rollup Field} & "")

Now empty rollups become empty strings, which AND() correctly treats as false.

Count shows 0 but records are linked

Possible causes:

  1. Wrong field selected: Make sure you're rolling up from the correct linked table
  2. Conditional filter too strict: Check if your conditions are excluding all records
  3. Using COUNT instead of COUNTALL: If records have empty values, use COUNTALL

Solution: Use COUNTALL(values) to count all linked records regardless of field values.

Rollup not updating

Check:

  • Are new records actually being linked?
  • Is the rollup field configured correctly?
  • Try refreshing the view or recalculating formulas

Note: Rollups update automatically when linked records change, but there may be a brief delay.

Best practices

Use COUNTALL for simple counts

When you just need to count linked records, COUNTALL(values) is the most reliable:

COUNTALL(values)

Use descriptive field names

Name your rollup fields clearly:

  • ✅ "Task Count"
  • ✅ "Number of Completed Tasks"
  • ❌ "Values" (conflicts with Airtable's internal naming)

Combine with conditions

Use conditional rollups to count specific subsets:

  • Count only completed tasks
  • Count tasks assigned to specific people
  • Count records created in a date range

Test with sample data

Before deploying:

  • Test with a few linked records
  • Verify the count matches expectations
  • Check edge cases (no linked records, all empty, etc.)

Common patterns

Count all linked records

COUNTALL(values)

Count records meeting a condition

COUNTALL(values)
  • Condition: Field = Value

Count records with data

COUNTA(values)

Count records with numeric data

COUNT(values)

Count unique values

COUNTALL(ARRAYUNIQUE(values))

Advanced: Counting in formula fields

You can also reference rollup fields in formula fields for more complex logic:

Check if count is above threshold

IF({Task Count} > 10, "Many tasks", "Few tasks")

Calculate percentage

{Completed Count} / {Total Count} * 100

Conditional text based on count

IF({Task Count} = 0, "No tasks",
  IF({Task Count} = 1, "1 task",
    {Task Count} & " tasks"
  )
)

Quick reference

Goal Rollup Formula Notes
Count all linked records COUNTALL(values) Most common
Count non-empty numbers COUNT(values) Numeric only
Count non-empty values COUNTA(values) Any type
Count with condition COUNTALL(values) + filter Use conditions
Sum values SUM(values) Numeric
Average values AVERAGE(values) Numeric
Join text ARRAYJOIN(values, ", ") With separator
Unique items ARRAYUNIQUE(values) Remove duplicates

Tips

  • Always use COUNTALL for simple record counting
  • Add conditions to count specific subsets
  • Name fields clearly to avoid confusion
  • Test thoroughly before relying on counts
  • Combine with formulas for complex logic
  • Remember: Rollups update automatically when links change

FAQ

What is the difference between COUNT, COUNTA, and COUNTALL in Airtable rollups?

COUNTALL(values) counts every linked record regardless of field content. COUNTA(values) counts only records where the rolled-up field has a non-empty value of any type. COUNT(values) counts only records with non-empty numeric values. For simple "how many records are linked?" questions, use COUNTALL.

Why does my rollup count show 0 even though records are linked?

Check three things: the rollup field is pointing to the correct linked record field, any conditional filters on the rollup are not excluding all records, and you are using COUNTALL(values) instead of COUNT(values). COUNT ignores non-numeric and empty values, which can return 0 even when records exist.

Can I count only linked records that meet a specific condition?

Yes. When configuring a rollup field, enable "Only include linked records that meet certain conditions" and set your filter (for example, Status = "Complete"). Then use COUNTALL(values) to count only the records that pass the filter.

Do rollup fields update automatically when linked records change?

Yes. Rollup fields recalculate whenever the linked records or the rolled-up field values change. There may be a brief delay, but no manual refresh is needed. If values seem stale, try reloading the view.

Build Better Airtable Forms with Filla

Rollup counts are only as useful as the data feeding into them. Filla helps you collect clean, linked records through forms that connect directly to your Airtable base.

  • Linked record fields on forms so submissions create proper relationships automatically
  • Required fields and validation rules to prevent incomplete entries
  • Conditional logic to show relevant fields based on category or type
  • Multi-step forms that guide submitters through structured data entry

Start free and connect your first form to any Airtable base.

Create Your First Form Free

References

Community discussion on counting lookup entries: Count the entries in a lookup field?

Official Airtable documentation: Rollup field overview