SUM of Hours column

bruD
bruD ✭
inFormulas and Functions

Hello, I have four columns:

Task | Hours | Assigned to | Total hours

The 'hours' column is how much time (hours) it will take to do the task, the 'assigned to' you can assign the task for more than one person.

Now I need the 'total hours' column to show me how much time it will take for a task (example: Task1 takes 1 hour but it is assigned to three people, so it need to be counted as 3 hours, 1 hours for each person). And in the end, a total sum of all the time of all tasks.

Can someone help me, please?

Thank you!

Tags:
· Share on Facebook Share on Twitter

Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would use something along the lines of...

    =COUNTM([Assigned To]@row) * Hours@row

    10xViz.com

    · Share on Facebook Share on Twitter
  • bruD
    bruD ✭
    You would use something along the lines of...<\/p>

    =COUNTM([Assigned To]@row) * Hours@row<\/p>","bodyRaw":"[{\"insert\":\"You would use something along the lines of...\\n=COUNTM([Assigned To]@row) * Hours@row \\n\"}]","format":"rich","dateInserted":"2023-03-14T13:40:46+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-22T11:03:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/367128#Comment_367128","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/367128#Comment_367128

    Thank you very much, it worked just as I needed!!

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help.️

    10xViz.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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Give this a try:<\/p>

=TODAY() - IF([Assigned Date]@row <> \"\", [Assigned Date]@row, DATEONLY(Created@row))<\/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":109212,"type":"question","name":"INDEX MATCH with multiple values question","excerpt":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets. There will be duplicate results for each column…","snippet":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker…","categoryID":322,"dateInserted":"2023-08-21T17:26:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T20:29:09+00:00","insertUserID":165425,"insertUser":{"userID":165425,"name":"MarcM","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MarcM","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T20:24:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3385290352,"url":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","format":"Rich","lastPost":{"discussionID":109212,"commentID":391719,"name":"Re: INDEX MATCH with multiple values question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391719#Comment_391719","dateInserted":"2023-08-21T20:29:09+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+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-08-21T20:28:55+00:00","dateAnswered":"2023-08-21T19:17:27+00:00","acceptedAnswers":[{"commentID":391690,"body":"

I think I would just use a COUNTIFS in this situation:<\/p>

=IF(COUNTIFS({FY24 Hirevue Response Sheet Range 1}, [Evaluator Name]@row, {FY24 Hirevue Response Sheet Range 2}, [HV Candidate Name]@row) > 0, 1, \"\")<\/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":109221,"type":"question","name":"Auto populate with current, past, or upcoming text based on year","excerpt":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or \"Upcoming\" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE) =IF(Year@row = YEAR(TODAY()),…","snippet":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or…","categoryID":322,"dateInserted":"2023-08-21T18:28:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165112,"lastUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":38,"score":null,"hot":3385288842,"url":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","format":"Rich","lastPost":{"discussionID":109221,"commentID":391688,"name":"Re: Auto populate with current, past, or upcoming text based on year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391688#Comment_391688","dateInserted":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+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-08-21T18:51:50+00:00","dateAnswered":"2023-08-21T18:50:14+00:00","acceptedAnswers":[{"commentID":391687,"body":"

See if this works for you:<\/p>

=IF(Year@row = YEAR(TODAY()), \"Current\", IF(Year@row < Year(today()),\"Past\", IF(Year@row > YEAR(today()), \"Upcoming\", 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

  • Formula Help

    I am trying to fix a formula. What I am trying to achieve is the number of days between the Assigned Date and the Created Date. The "Executed" is for when the status changes to Executed. I have a status column and date columns following for the statuses like "Assigned" , "Pending" etc. and for each status is a date column…

  • INDEX MATCH with multiple values question

    Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets. There will be duplicate results for each column…

  • Auto populate with current, past, or upcoming text based on year

    I am trying to auto populate a column with "Current", "Past", or "Upcoming" based on the year column. So for a row with 2023, I would want "Current", but would want "Past" or "Upcoming" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE) =IF(Year@row = YEAR(TODAY()),…