COLLECT Function

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.
    • criterion_range2
    • criterion2
    • ...
    —[optional]
    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

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community