Formula Question

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.
Best Answer
-
First, in the sample pending days sheet, below, I added a helper column, [Pending Days] with the following formula;
=TODAY() - [Open Date]@row
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.
Answers
-
First, in the sample pending days sheet, below, I added a helper column, [Pending Days] with the following formula;
=TODAY() - [Open Date]@row
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.
-
Thank you! I think this is going to work. Appreciate your time on this!!
-
Happy to help!π
Help Article Resources
Categories
Check out the Formula Handbook template!