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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…