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))
  • 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
    • ...
    Additional ranges and criteria to have evaluated. All criteria must be met to be collected.
Usage notes
  • COLLECT must be used within another function.

This example references the following sheet information:

 Clothing ItemTransaction TotalUnits SoldIn Stock?

Given the table above, here are some examples of using COLLECT in a sheet:

=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