Used within another function to collect specific values in a range that meet the provided criteria
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.
- criterion_range2, criterion2, ... —[optional]Additional ranges and criteria to have evaluated. All criteria must be met to be collected.
Sample usage
AVG(COLLECT(Cost1:Cost5, Cost1:Cost5, > 30))
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 |
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.