Formulas

Heather Dixon

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!

 

Shaine Greenwood

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

Heather Dixon

Hi - Thanks I have reviewed the above links already, where I'm confused is how to execute these formulas in my sheet.

Thanks,

Heather

Chris McKay

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

Heather Dixon

Hi Chris,

Thank you so much for your response.  I figured the formula out and have completed my dashboard.

Thanks,

Heather