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