Conditional Collect Formula

adb ✭✭
edited 12/09/19 in Formulas and Functions



I am attempting to sum a range of numbers that meet multiple conditions.

Data is input into my sheet with a time stamp and a box type(C1,CX etc.)

I would like to sum the numbers that are of each unique box type AND are the first 10 timestamped inputs of the day. 

That is, sum the first 10 C1 inputs of the day, first 10 CX inputs of the day etc. 


I thought the solution would require a collect function, however, I am returning 0 when suming the values that meet both the Box Type and Date conditions. 


My Attempt:

=SUM(COLLECT({Lines}, {Date}, <=SMALL({Date}, 10), {Intake Form Range 12}, ="C1"))






  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try using a SUMIFS instead and see how that works for you.

  • eric.o
    eric.o Employee
    edited 01/09/19



    Happy to help! In order to achieve this desired goal, you would want to distinguish the first 10 entries somehow. For example, utilizing a symbol column to manually determine the entry is one of ten or utilize a numbered structure to determine the first 1-10. This would be determined based on the workflow of how the tasks/items appear on the sheet. Next as stated above you could utilize a SUMIFS function to achieve this desired goal. Steps to utilize SUMIFS are further outlined here:


    Note: Utilizing the time of day may not work because we currently do not have a method to calculate time. 




    Smartsheet Support

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/09/19

    Using the Created (date) type column will allow time of day to be used for the COLLECT(SMALL( criteria, however I don't know exactly how that would handle multiple matching entries. I noticed that down at the bottom of your screenshot you have 3 identical entries. I am not sure exactly how that would affect your SMALL function. If you were to build in an auto-numbering system, you could you that in addition to or in place of the created date to truly gather the 10 most recent entries.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!