Productivity & Workflows
Airtable Lookup or Rollup? Summarize Linked Work Without Counting the Wrong Records
Complete seven-task/two-project ledger and five-operation diagnostic distinguish visible records, conditional aggregation, blank/zero estimates, duplicate work and shared portfolio scope.
Choose a Lookup when you need the individual values from linked work; choose a Rollup when you need a calculation across those values. Before trusting either, check which records are actually linked, which field is being read, and which conditions apply. A filtered task view can hide records that still contribute to a project total.
This LandOffer guide uses a complete fictional project dataset to reconcile those choices. Airtable's official documentation was checked on October 25, 2026. All records, proposed fields, and review decisions below are authored examples; no real Base, record, formula, API, or account was accessed or changed. A separate offline arithmetic check verifies the example numbers, not Airtable execution.

Define what the number is meant to represent
Write the reporting question before choosing the field type. “Hours” might mean estimates for all linked tasks, estimates excluding canceled work, completed effort, or unique work across several projects. Those are different populations. Write down the unit, inclusion rule, treatment of missing values, and whether shared work should count once or once per project association.
Airtable's Lookup overview describes values collected through an existing linked-record relationship. An existing link field is only the structure; the particular project and task records must also be connected. A Lookup is useful when someone must inspect the underlying task names or statuses instead of receiving only a scalar total. Do not interpret a visually comma-separated list as a reliable numeric sum.
A Rollup selects a linked-record source, a field on those records, and an aggregation formula. For this article's numeric estimates, the proposed aggregation is SUM(values). The formula's simplicity does not settle whether the relationships and inclusion policy are correct.
Name fields after their scope: “Linked task estimates” for the Lookup and “Non-canceled estimate hours” for a conditional total. A scope-specific label helps the next reader distinguish estimates from measured time and canceled history from current work. A summary labeled “completed hours” would be misleading if it merely sums estimates from completed tasks; completing a task does not turn its estimate into measured time.
Keep the source field numeric and its meaning stable. Do not repair a suspicious result by copying a displayed total into an editable number field. That creates a second maintained value without resolving the wrong link, blank estimate, duplicate work item, or stale condition that caused the disagreement.
Case one: a complete project relationship and its results
The fictional Projects table has P17, Archive Catalog, and P18, Catalog QA. The Tasks table has seven records. Hours are estimates, and a blank means the estimate is unknown. Work key identifies the underlying work item; task ID identifies this particular stored record.
| Task ID / work key | Task | Status | Estimate hours | Linked project(s) |
|---|---|---|---|---|
| T11 / W11 | Index checklist | Done | 2 | P17 |
| T12 / W12 | Shared source review | In progress | 3 | P17, P18 |
| T13 / W13 | Exception notes | Planned | Blank | P17 |
| T14 / W14 | Reuse existing labels | Done | 0 | P17 |
| T15 / W15 | Retired appendix | Canceled | 5 | P17 |
| T16 / W16 | QA sampling | Planned | 4 | P18 |
| T17 / W12 | Shared source review copy | In progress | 3 | P18 |
The relationship map is explicit: P17 links to T11, T12, T13, T14, and T15. P18 links to T12, T16, and T17. T12's two project associations are intentional. T17 is a separate stored record with the same work key as T12; the reviewer suspects duplicate work data. These are distinct problems, not repeated text in a displayed field.
For P17, a task-name Lookup would bring through Index checklist, Shared source review, Exception notes, Reuse existing labels, and Retired appendix. An estimate Lookup provides the corresponding source values: 2, 3, blank, 0, and 5. This is a provenance worksheet in task-ID order, not a claim about Airtable's exact blank rendering or item ordering in a particular client.
The unconditioned estimate Rollup has an expected arithmetic total of 10: 2 + 3 + 0 + 5. It includes the canceled appendix because T15 remains linked and there is no exclusion condition. T13 remains unestimated; the total of known numbers does not supply its missing estimate.
Airtable documents different counting functions: COUNT(values) counts non-empty numeric values, COUNTA(values) counts non-empty values, and COUNTALL(values) counts linked records, including blanks. Applied to this simple numeric-estimate example, the expected results are 4, 4, and 5 respectively. Zero is a known numeric estimate, so T14 is not a missing estimate.
The owner, Celia, needs a non-canceled estimate subtotal, not all linked estimates. She proposes an explicit condition excluding Status = Canceled on the summary field. Its expected included IDs are T11–T14, its linked-task count is 4, its known numeric estimates number 3, and its known-hours subtotal is 5. The accompanying missing-estimate count is 1. Celia's completed definition therefore reads: “5 known estimate hours across 4 non-canceled tasks; 1 task still unestimated.”
This wording preserves the uncertainty. It does not report five hours as the project's full forecast or imply the zero-hour task was forgotten. Celia leaves the actual field creation and account verification pending an authorized owner review.
Case two: hiding, filtering, and duplicate work change different things

A teammate sees only T11–T14 in an “Active tasks” view and expects P17's existing unconditioned total to become 5. That view hides the canceled appendix, but the summary still has T15 in its linked population. Airtable's Rollup documentation explicitly says filtering a source table's view does not, by default, change the Rollup of those linked records.
The completed diagnostic record separates each operation:
| Proposed operation | P17 included linked IDs for the summary | Expected known estimate total | Meaning |
|---|---|---|---|
| Hide T15 through an Active tasks view | T11–T15 for unchanged Rollup | 10 | Presentation changed; aggregation scope did not |
| Add summary condition Status is not Canceled | T11–T14 | 5 | Canceled task excluded by the summary itself |
| Add summary condition Status is Done | T11, T14 | 2 | Completed-task estimates only, not actual time |
| Merely restrict the record picker to Active tasks | Existing links unchanged | 10 for unchanged Rollup | Selection aid is not retroactive cleanup |
| Propose unlinking T15 | T11–T14 if approved | 5 | Relationship history changes; not chosen here |
Airtable's linking-records guidance says a picker view restriction limits what is visible during manual selection; it does not prevent records outside that view from being linked. A picker restriction therefore does not establish the same guarantee as a conditional aggregation. Celia keeps T15 linked for historical context and proposes the summary condition instead of unlinking it to force the desired number.
The duplicate investigation happens in P18. Its three stored links produce 3 + 4 + 3 = 10 known estimate hours. But T12 and T17 both claim work key W12. After comparing the fictional scope notes, Celia concludes T17 is a duplicate representation of T12, not a separate three-hour task. The proposed corrected P18 set is T12 and T16, totaling 7; actual records remain unchanged.
A deduplicated list of hour values is the wrong repair. Two genuinely different tasks could both estimate three hours and both deserve inclusion. Conversely, duplicate task records can contain different stale estimates. Numeric equality cannot decide work identity, so the review retains task IDs and work keys alongside values.
The received link manifest also repeats T12 twice for P18. The local reconciliation flags that repeated ID separately from the distinct T17 record. It normalizes the manifest to unique task IDs before computing the table's baseline; it makes no claim that Airtable permits two copies of the same record link in one cell. This avoids inventing product behavior to explain an upstream data problem.
Check the portfolio denominator before adding project totals
Adding the original non-canceled P17 subtotal of 5 to P18's duplicate-contaminated 10 yields 15. That number contains two issues: T17 duplicates W12, and T12 legitimately participates in both project summaries. Fixing the duplicate alone gives project subtotals of 5 and 7, whose sum is still 12.
For a portfolio question defined as unique underlying work, the agreed included work keys are W11, W12, W13, W14, and W16. Their known estimates are 2, 3, blank, 0, and 4, totaling 9 known hours with one unestimated work item. The difference between 12 and 9 is the shared W12 counted through both projects. Both project subtotals can be valid while their sum answers the wrong portfolio question.
Celia's completed decision memo preserves both measures: “Use 5 and proposed 7 for each project's linked work context; do not label their sum unique portfolio effort. Proposed unique-work subtotal is 9 known hours plus one missing estimate. T17 duplicate resolution and authoritative W12 ownership require approval before any record correction.” The per-project and portfolio counts intentionally use different denominators.
The proposed review preserves work records while resolving identity and reporting scope. If later review shows T17 represents a separate deliverable, it needs its own distinct work identity and scope evidence, after which the expected results must be recalculated. If shared effort should instead be allocated across projects, agree on an allocation model; do not silently split three hours in half without an owner-approved rule.
The offline fixture checks the stated links, repeated manifest ID, known totals, numeric counts, and unique-work set. It does not execute Airtable formulas, reproduce its display, validate permissions, or determine whether these estimates reflect real work. The fictional record review and actual local arithmetic are separate evidence.
Configure conditions with a complete verification record
Airtable's conditional-field guidance describes filtering Lookup, Count, and Rollup fields to a subset of their linked records. Record the actual field, operator, comparison value, and grouping of multiple conditions. “Use the Active view” is not a replacement for stating Status is not Canceled in the proposed summary configuration.
Before an authorized owner edits a Base, preserve the current field definition, source link, source value field, formula, and conditions. Compare expected IDs and results with observations from the account. Include the unknown estimate, known zero, canceled record, suspected duplicate, and shared task in the verification sample. Compare the included IDs as well as the total, because two scope mistakes can offset each other numerically.
If the summary goes blank, inspect relationships and configuration before replacing blanks with zero. The conditional-field documentation notes that deleting a referenced field or changing its type can invalidate a condition; a field that becomes invalid after creation can show blank cells. A blank can therefore signal configuration trouble rather than a legitimate empty population.
The completed handoff for this example names Celia as the reviewer and the Base owner as the person who must approve and verify changes. It attaches the seven-task ledger, link manifest, expected ID sets, and calculations. The observed account-result column is explicitly unfilled because no account test occurred. Changing labels, links, fields, or formulas remains outside this article's actions.
Close with a scope statement, not an unexplained total
A trustworthy summary states the population and the remaining gaps. Here, P17 has 5 known non-canceled estimate hours across 4 tasks with 1 estimate missing. P18's proposed corrected total is 7 across 2 distinct task records. The unique-work portfolio subtotal is 9 with 1 estimate missing; those outputs answer different questions.
Save that definition beside the field's review record so a later owner can understand why canceled history stays linked and why project subtotals are not automatically additive. Revisit the definition when work identity, status choices, relationships, or reporting needs change. For related workplace data and coordination guides, return to LandOffer after preserving the expected results and pending verification.

