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 ranges.

ItemSupplyCost
T-Shirt520.00
Socks435.75
Jacket2200.25
Shorts2830.50
Pants1450.00

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

FormulaDescriptionResult
=AVG(COLLECT(Cost1:Cost5, Cost1:Cost5, >10.00))Average Cost if it's greater than 10.0075.1875
=MAX(COLLECT(Cost1:Cost5, Supply1:Supply5, >20))Max Cost with Supply greater than 20.0030.50
=JOIN(COLLECT(Item1:Item5, Cost1:Cost5, > 20.00), ", ")Join Items with comma and space with Cost greater than 20.00Jacket, Shorts, Pants
Was this article helpful?
YesNo