Formula Question

rachel_thompson1

Needing help creating an average formula that cross-references sheets. I have created a metrics sheet for this task.

I need a formula that calculates the average pending days an item has been open, with an assigned task level of '1', for a specific type of category, that is specifically assigned to an individual person.

jmyzk_cloudsmart_jp

Hi @rachel_thompson1

First, in the sample pending days sheet, below, I added a helper column, [Pending Days] with the following formula;

=TODAY() - [Open Date]@row

https://app.smartsheet.com/b/publish?EQBCT=a21989f25b2b4be1ad24eb828a8836e4

Then, in the metric sheet, I used the following formula to calculate the sum of pending days.

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), 
SUMIFS({Pending Days}, 
  {Assinged To}, PARENT([Assigned To]@row), 
  {Category}, Category@row, 
  {Task Level}, 1))

The if part is because I used a hierarchical structure.

Then, the SUMIFS functions calculate the sum of the pending days that meet the conditions.

https://app.smartsheet.com/b/publish?EQBCT=424714b383bd4d94a08a4290b0ee9e10

jmyzk_cloudsmart_jp

Hi @rachel_thompson1

First, in the sample pending days sheet, below, I added a helper column, [Pending Days] with the following formula;

=TODAY() - [Open Date]@row

https://app.smartsheet.com/b/publish?EQBCT=a21989f25b2b4be1ad24eb828a8836e4

Then, in the metric sheet, I used the following formula to calculate the sum of pending days.

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), 
SUMIFS({Pending Days}, 
  {Assinged To}, PARENT([Assigned To]@row), 
  {Category}, Category@row, 
  {Task Level}, 1))

The if part is because I used a hierarchical structure.

Then, the SUMIFS functions calculate the sum of the pending days that meet the conditions.

https://app.smartsheet.com/b/publish?EQBCT=424714b383bd4d94a08a4290b0ee9e10

rachel_thompson1

Thank you! I think this is going to work. Appreciate your time on this!!

jmyzk_cloudsmart_jp

Happy to help!😁