Formulas
I'm completely stuck on this one and not even sure it is possible. I am trying to compile data from a sheet to be used in a dashboard to show total time to complete "phases" of a project.
I need a formula that is able to determine "Active" clients and the "Phase" that they are in, and then the processing time for that phase. Because I have Parent/Children, I am not sure how to create this formula to meet all the criteria.
Status = Active
Phase = Discovery
Total Time to Complete = Number is corresponding children row under Firm Name
I have attached a screen shot of the sheet for reference.
Any help is greatly appreciated!
Comments
-
Hi Heather,
It looks like you may be wanting to sum this information depending on specific criteria. I'd recommend a SUMIFS function, which will only sum numbers in one column or range if other criteria are met in other columns. More info on SUMIFS is available in the help center: https://help.smartsheet.com/function/sumifs
Since you have hierarchy in your sheet, you may also want to use the CHILDREN function in your formula: https://help.smartsheet.com/function/children
It looks like you also might want to also have a count of the number of phases as well? If you're wanting to count text values, then I'd recommend using the COUNTIF function (as opposed to SUMIF): https://help.smartsheet.com/function/countif
-
Hi - Thanks I have reviewed the above links already, where I'm confused is how to execute these formulas in my sheet.
Thanks,
Heather
-
Hi Heather,
I'm happy to take a crack at it if you're still stuck but will need a bit more info.
Do your 2 screenshots represent 2 different sheets? It looks like your first sheet's total time is a SUM of child items for a project on another sheet, but I could be wrong.
Kind regards,
Chris McKay
-
Hi Chris,
Thank you so much for your response. I figured the formula out and have completed my dashboard.
Thanks,
Heather
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!