Sumifs syntax help

trying to get a sum for the number of OT sessions for each student in a given week

not sure what's wrong with my formula

=SUMIFS([Todays Week ]@row, {Week Number}, [Student Code]@row, {OT Student Code}, {OT Sessions},=>1)

I'm trying to sum the number in the OT session column of a separate sheet if the week number matches matches todays week column and the student code in my row matches with the student code in the separate sheet. I dont think i need the =>1 in the sessions count because if there's a 0 or no valuse the session log it should still calculate but I couldn't get it to work and I thought it needed some kind of criterion there

thanks

Tags:

Best Answer

  • Vojtech Gajdos
    Answer ✓

    Hi@Dell55, there is something weird in your formula (the arguments being jumbled up) - the first argument of SUMIFS is the range of cells you are trying to sum up and - I guess it's not the [TODAY Week]? To me the formula should rather be:

    =SUMIFS({OT Sessions}, {Week Number}, [Todays Week ]@row, {OT Student Code}, [Student Code]@row)

    Hope it makes sense but pls get back if unclear

    Regards

    Vojtech

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @Hobo<\/a> ,<\/p>


<\/p>

You can use this formula. The formula will count rows where priority is high and status is not completed.<\/p>


<\/p>

=COUNTIFS(Priority:Priority, \"High\", Status:Status, <> \"Completed\")<\/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":112197,"type":"question","name":"Method to check whether a Assessment Date has passed upon multiple cells within a row.","excerpt":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is…","snippet":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular…","categoryID":322,"dateInserted":"2023-10-26T13:07:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:51:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":143323,"lastUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:51:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":54,"score":null,"hot":3396731177,"url":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","format":"Rich","lastPost":{"discussionID":112197,"commentID":401973,"name":"Re: Method to check whether a Assessment Date has passed upon multiple cells within a row.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401973#Comment_401973","dateInserted":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:51:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T10:10:19+00:00","dateAnswered":"2023-10-26T14:43:35+00:00","acceptedAnswers":[{"commentID":401827,"body":"

@Matthew Drake<\/a> <\/p>

If you wanted to build a nested IF statement, you would add RYG ball Symbol column and use a formula something like this:<\/p>

=IF(AND(TODAY() > [Knowledge\/SJT\/MCQ\/Skills Test Date & Time]@row, ISBLANK([Knowledge Test Result]@row)), \"Red\", IF(AND(TODAY() > [Project \/ Showcase Portfolio]@row, ISBLANK([Project \/ Showcase Portfolio Results]@row)), \"Red\", \"Green\"))<\/p>

You could keep building this out to evaluate all of your date \/ result fields, but you could run into issues where a date is not yet entered.<\/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":112223,"type":"question","name":"How to pull the most recent status symbol from another sheet?","excerpt":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date\/symbol, that symbol should be automatically updated…","snippet":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th…","categoryID":322,"dateInserted":"2023-10-26T17:10:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T20:32:57+00:00","insertUserID":169134,"insertUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T20:42:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":133754,"lastUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3396693219,"url":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","format":"Rich","lastPost":{"discussionID":112223,"commentID":401926,"name":"Re: How to pull the most recent status symbol from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401926#Comment_401926","dateInserted":"2023-10-26T20:32:57+00:00","insertUserID":133754,"insertUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"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\/F30QLRHNUSZM\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T20:42:55+00:00","dateAnswered":"2023-10-26T20:32:57+00:00","acceptedAnswers":[{"commentID":401926,"body":"

Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 0))<\/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":[]}],"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