Conditional Collect Formula

adb
adb ✭✭
edited 12/09/19 inFormulas and Functions

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"))

Sketch.png

Tags:
· Share on Facebook Share on Twitter

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

    · Share on Facebook Share on Twitter
  • eric.o
    eric.o Employee
    edited 01/09/19

    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

    · Share on Facebook Share on Twitter
  • 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.

    thinkspi.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Thank you, as always I was trying to make it too hard!!!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106971,"type":"question","name":"Using SUMIFS Formula","excerpt":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!","snippet":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of…","categoryID":322,"dateInserted":"2023-06-27T18:45:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162642,"lastUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":26,"score":null,"hot":3375791135,"url":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106971,"commentID":382695,"name":"Re: Using SUMIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382695#Comment_382695","dateInserted":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T20:19:39+00:00","dateAnswered":"2023-06-27T19:59:16+00:00","acceptedAnswers":[{"commentID":382688,"body":"

Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/p>

When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":55,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • 使用Countif to count Often and Very Often,

    however I can't get it to work the Often is also counting the Very Often: =COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, HAS("Often", @cell), NOT(HAS("Very", @cell)))

  • Using SUMIFS Formula

    Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, "AMR", {leadership}, "Truttmann")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!

  • IF cell CONTAINS one/two/three different values, return corresponding values

    Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New" Currently the formula works for single values with the following formula =IF([Type of Project]@row = "Consolidation", "",…