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 | Clothing Item T-Shirt | Transaction Total 1,170.00 | Units Sold 78 | In Stock? Brandfolder Image
![]() |
2 | Clothing Item Pants | Transaction Total 1,491.00 | Units Sold 42 | In Stock? Brandfolder Image
![]() |
3 | Clothing Item Jacket | Transaction Total 812.00 | Units Sold 217 | In Stock? Brandfolder Image
![]() |
Given the table above, here are some examples of using COLLECT in a sheet:
Formula | Description | Result |
---|---|---|
Formula =AVG(COLLECT([Units Sold]1:[Units Sold]3, [In Stock?]1:[In Stock?]3, 0, [Transaction Total]1:[Transaction Total]3, >1000 )) | Description Averages the Units Sold column if the In Stock? column is unchecked and Transaction Total exceeds 1,000. Rows 1 and 2 apply. | Result 60 |
Formula =JOIN(COLLECT([Clothing Item]1:[Clothing Item]3, [In Stock?]1:[In Stock?]3, 0), ", ") | Description 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. | Result T-Shirt, Pants |
Formula =MIN(COLLECT([Transaction Total]1:[Transaction Total]3, [Units Sold]1:[Units Sold]3, >50)) | Description Returns the lowest number from the Transaction Total column where more than fifty units were sold. Row 3 applies. | Result 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.