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 ItemTransaction TotalUnits SoldIn Stock?
1T-Shirt1,170.0078false
2Pants1,491.0042false
3Jacket812.00217true

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

FormulaDescriptionResult
=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
Was this article helpful?
YesNo