Used within another function to select specific values in a range that meets the provided criteria
Syntax
COLLECT(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
- range—The group of values to include in the final list.
- criterion_range1—The group of cells to be evaluated by the criterion.
- criterion1—The condition that defines which values to collect.
- criterion_range2, criterion2, ... —[optional]Additional ranges and criteria to have evaluated. All criteria must be met to be collected.
Sample usage
MIN(COLLECT(Cost1:Cost5, Cost1:Cost5, > 0))
JOIN(COLLECT(Name1:Name5, Status1:Status5, "Active"), ", ")
Usage notes
- Use JOIN(COLLECT(), "- ") to see all values and/or error messages produced by the COLLECT for troubleshooting.
- COLLECT must be used within another function as the primary range element.
- All listed criteria must be met to add the value to the range.
- Range and Criterion_ranges must be the same size and in the same sheet.
- If the range to collect and criteron_range are the same, they must be listed twice.
- Errors with COLLECT may not be visible when used with another function, such as COUNT.
- COUNT(COLLECT()) functions with errors will give a result of 1, as COUNT will detect the single error value and count it instead of carrying it forward
Examples
This example references the following sheet information:
| Clothing Item | Transaction Total | Units Sold | In Stock? | |
|---|---|---|---|---|
| 1 | T-Shirt | 1,170.00 | 78 | Brandfolder Image
|
| 2 | Pants | 1,491.00 | 42 | Brandfolder Image
|
| 3 | Jacket | 812.00 | 217 | Brandfolder Image
|
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, [Transaction Total]1:[Transaction Total]3, >1000 )) | Averages the Units Sold column if the In Stock? column is unchecked and Transaction Total exceeds 1,000. 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 unchecked, separating the values with a comma and a space. Rows 1 and 2 apply. | T-Shirt, Pants |
| =MIN(COLLECT([Transaction Total]1:[Transaction Total]3, [Units Sold]1:[Units Sold]3, >50)) | Returns the lowest number from the Transaction Total column where more than fifty units were sold. Row 3 applies. | 812.00 |
Still need help?
Use the Formula Handbook template to find more support resources, view 100+ formulas, review a glossary of every function that you can practice working with in real time, and find examples of commonly used and advanced formulas.
You can find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.