Conditional Collect Formula

adb

Hello,

 

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

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

eric.o

Hello,

 

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: https://help.smartsheet.com/function/sumifs

 

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

 

Cheers, 

Eric  

Smartsheet Support

Paul Newcome

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.