Used within another function to collect specific values in a range that meet the provided criteria.
Sample Usage
AVG(COLLECT(Cost1:Cost5, Cost1:Cost5, > 30))
Syntax
COLLECT(
- range
- criterion_range1
- criterion1
- []
- criterion_range2
- criterion2
- ...
- range—The group of values to collect.
- criterion_range1—The group of cells to be evaluated by the criterion.
- criterion1—The condition that defines which values to collect.
- —[optional]
- criterion_range2
- criterion2
- ...
Additional ranges and criteria to have evaluated. All criteria must be met to be collected.
Usage Notes
- COLLECT must be used within another function.
Examples
This example references the following sheet information:
| Clothing Item | Transaction Total | Units Sold | In Stock? | |
|---|---|---|---|---|
| 1 | T-Shirt | 1,170.00 | 78 | false |
| 2 | Pants | 1,491.00 | 42 | false |
| 3 | Jacket | 812.00 | 217 | true |
Given the table above, here are some examples of using COLLECT in a sheet:
| Formula | Description | Result |
|---|---|---|
| =AVG(COLLECT([Units Sold]1:[Units Sold]3, [In Stock?]1:[In Stock?]3, 0)) | Averages the “Units Sold” column if the “In Stock” column is false. Rows 1 and 2 apply. | 60 |
| =JOIN(COLLECT([Clothing Item]1:[Clothing Item]3, [In Stock?]1:[In Stock?]3, 0), ", ") | Joins the values in the “Clothing Item” column if the “In Stock” column is false, separating the values with a comma and a space. Row 1 & 2 apply. | T-Shirt, Pants |
| =MIN(COLLECT([Transaction Total]1:[Transaction Total]3, [In Stock?]1:[In Stock?]3), 1)) | Returns the lowest number from the “Transaction Total” column for anything that has a corresponding “In Stock” value of true. Row 3 applies. | 812.00 |