first
To sum up the actual time completed by each assigned rep in Smartsheet, use the SUMIFS function. The SUMIFS function is a function that sums values based on multiple criteria.
Question summary
EJ End asks how to add up the actual time completed by each assigned rep. EJ End, I'm currently using a formula that calculates the total time allocated to each person, but I would also like to display the amount of time each person has completed and how many hours still need to be completed. (The original Community article isplease use this form.. )
Answer summary
Best answer jmyzk_cloudsmart_jp suggests changing each group of SUMIF functions to SUMIFS functions and adding status conditions to solve EJ End's problem.
Specifically, use the following formula:
SUMIFS({estimated time 3}, {assigned to 3}, Primary24, {status},”completed”)
This formula sums the values in the {Estimated Time 3} column where the {Assigned To 24} column has a value of "Primary3" and the {Status} column has a value of "Complete."
You can also use the COLLECT function to create a range containing status conditions for use with the SUM function. In this case, use the following formula:
SUM(COLLECT{estimated time 3}, {assigned to 3}, Primary24, {status},”completed”))
This formula uses the COLLECT function to calculate the value of the {Estimated Time 3} column when the {Assigned To 24} column has a value of "Primary3" and the {Status} column has a value of "Complete". Create a range. Then use the SUM function to calculate the sum of this range.
Summary
To sum up the actual time completed by each assigned rep in Smartsheet, use the SUMIFS or COLLECT functions. Use these functions to easily understand the completion status of each rep.
翻 訳
Currently I can calculate the total time allocated to each person, but I would like to show how much time each person has completed and how many hours still need to be completed. This is the formula I am using to calculate the total time allocated and this is working. =SUMIF({assignee 3}, Primary24, {estimated number of hours 3}) + SUMIF({assignee 4}, Primary24, {estimated number of hours 4}) + SUMIF({assignee 5}, Primary24, {estimated time Number 5} ) + SUMIF({assigned to}, Primary24, {estimated time}). Next, I would like to add the aspect of whether the status is completed or not. If you have any ideas, please let me know.
best answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
12/23/23
Hello@EJ end
Change each group of SUMIF to SUMIFS, add a status condition, and move the range forward {estimated time #}.
for example;
SUMIFS({estimated time 3}, {assigned to 3}, Primary24, {status},”complete”)
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
12/23/23
Hello@EJ end
Change each group of SUMIF to SUMIFS, add a status condition, and move the range forward {estimated time #}.
for example;
SUMIFS({estimated time 3}, {assigned to 3}, Primary24, {status},”complete”)
SUMIFS Function | Smartsheet Learning Center
https://help.smartsheet.com/function/sumifs
Adds numbers in a range if all specified conditions within each range are met.Alternatively, use COLLECT to create a range containing status conditions for use with the SUM function.
For example; SUM(COLLECT{estimated time 3}, {assigned to 3}, Primary24, {status},”completed”))
answer
It. Lavajante ✭✭✭✭✭
12/23/23
Hello@EJ end
Use the SUMIFS function to sum values based on multiple criteria.
I hope this helps! 🙂
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
12/23/23
Hello@EJ end
Change each group of SUMIF to SUMIFS, add a status condition, and move the range forward {estimated time #}.
for example;
SUMIFS({estimated time 3}, {assigned to 3}, Primary24, {status},”complete”)
SUMIFS Function | Smartsheet Learning Center
https://help.smartsheet.com/function/sumifs
Adds numbers in a range if all specified conditions within each range are met.
Alternatively, use COLLECT to create a range containing status conditions for use with the SUM function.
for example;
SUM(COLLECT{estimated time 3}, {assigned to 3}, Primary24, {status},”completed”))
EJ end ✭✭✭
12/29/23
It went well!