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

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

Step 2: Configure the rollup

Select rollup source: Choose the table with linked records (e.g., "Tasks")
Select field to roll up: Choose any field from the linked table (for counting, it doesn't matter which field)
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

In rollup field configuration
Check "Only include linked records that meet certain conditions"
Set your filter conditions
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:

Wrong field selected: Make sure you're rolling up from the correct linked table
Conditional filter too strict: Check if your conditions are excluding all records
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

References

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

Official Airtable documentation: Rollup field overview

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

Count entries in lookup fields with rollups

What is a rollup field?

Counting entries in lookup fields

COUNTALL — Count all linked records

COUNT — Count non-empty numeric values

COUNTA — Count non-empty values (any type)

Comparison: COUNT vs COUNTALL vs COUNTA

Setting up a rollup field to count

Step 1: Add a rollup field

Step 2: Configure the rollup

Step 3: Create the field

Common counting scenarios

Count linked records

Count records with specific values

Count records with numeric data

Count records with any data

Complete rollup function reference

Math functions

Text functions

Array functions

Logic functions

Conditional rollups (filtering)

Add conditions

Troubleshooting

Empty rollups return true in AND()

Count shows 0 but records are linked

Rollup not updating

Best practices

Use COUNTALL for simple counts

Use descriptive field names

Combine with conditions

Test with sample data

Common patterns

Count all linked records

Count records meeting a condition

Count records with data

Count records with numeric data

Count unique values

Advanced: Counting in formula fields

Check if count is above threshold

Calculate percentage

Conditional text based on count

Quick reference

Tips

References