By default, a filter applied in one table does not affect a rollup in another table. A rollup field will roll up data from all records in the target linked table, regardless of whether or not those records have been filtered in some way.
If you only want to rollup certain linked records in each cell, you will need to use IF formulas to make conditional rollups. Here's an example of how:
Example (One criterion, one IF statement)
Suppose you have a table of sales, with a field for the dollar amount of each sale and a field for whether that sale was made in cash or credit.
That table is linked to another table of retail locations:
If you wanted to have a rollup field in the Retail Locations table that ONLY summed the sales that were made in cash at each location, you would need to make a new field on the Sales table with the formula IF({Type of Sale} = "Cash", {Amount of Sale}, 0).
In plain English, this means, "If what it says in the 'Type of Sale' field is 'Cash,' then display the information from the 'Amount of Sale' field. Otherwise, display the number '0.'"
IF({Type of Sale} = "Cash", {Amount of Sale}, 0)
Then, on the Retail Locations table, you would make a rollup field that's linked to the new formula field you created in the Sales table, "If cash."
Example (Two criteria, nested IF statements)
Now suppose you wanted to do a rollup of ONLY cash sales greater than $100. If this were the case, you would need to change the formula in the Sales table to reflect multiple criteria. To do this, you can modify the formula by placing another IF statement inside the original IF statement—this is called "nested IF statements." The next IF statement goes in the slot of the value that is displayed if the logical argument is true.
So the formula IF({Type of Sale} = "Cash", IF({Amount of Sale} > 100, {Amount of Sale}, 0), 0) translates to, "If what it says in the 'Type of Sale' field is 'Cash,' and if the value in the "Amount of Sale" field is greater than 100, then display the information from the 'Amount of Sale' field. Otherwise, display the number '0.'"
IF({Type of Sale} = "Cash", IF({Amount of Sale} > 100, {Amount of Sale}, 0), 0)
Summary
If you wish to use a rollup field, but to have it only aggregate values from certain linked records in each cell, you will need to:
- Create a new formula field using the form IF(filter condition, {field value}, 0). You may also need to use nested IF statements if you wish to select by multiple criteria.
- Rollup using the newly created formula field rather than the original field.