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 ranges.
| Item | Supply | Cost |
|---|---|---|
| T-Shirt | 5 | 20.00 |
| Socks | 43 | 5.75 |
| Jacket | 2 | 200.25 |
| Shorts | 28 | 30.50 |
| Pants | 14 | 50.00 |
Given the table above, here are some examples of using COLLECT in a sheet:
| Formula | Description | Result |
|---|---|---|
| =AVG(COLLECT(Cost1:Cost5, Cost1:Cost5, >10.00)) | Average Cost if it's greater than 10.00 | 75.1875 |
| =MAX(COLLECT(Cost1:Cost5, Supply1:Supply5, >20)) | Max Cost with Supply greater than 20.00 | 30.50 |
| =JOIN(COLLECT(Item1:Item5, Cost1:Cost5, > 20.00), ", ") | Join Items with comma and space with Cost greater than 20.00 | Jacket, Shorts, Pants |