Master Metric sheet. Multiple users to 1 row

Options

Hello

I am using a metric sheet to show owners total tasks in their name based on status (initiate and overdue). The first column is owners name.

All of the tasks have 1 owner only except for 1 row which has multiple owners. How do i make sure their metric is included in their total calculation below?

Each row below is shown as a metric on our dashboard. The users name and how many tasks in their name that has the below status.


Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Is the owner name a multiselect drop down? How are you calculating the single-owner rows?

    If you are using a multislect and a function such as COUNTIF or SUMIF, you can use the HAS function within the criteria. This would then work on both of the names mentioned in a multi-owned project.

  • Laura G
    Laura G ✭✭
    Options

    Hi KPH

    The owner column on the sheet is a contact list that you can select multiple owners.

    On the master metric sheet (picture above) I am using a reference formula =COUNTIFS({SHEET1}, $[Primary Column]@row, {SHEET2}, [Column1]$17)

    This is the formula i use on my metrics sheet (picture above). The Primary Column is the owner, Column1 is the status. eg: Initiate

    How would include the HAS function on the metrics sheet?

    Thanks so much

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi @Laura G

    Try

    =COUNTIFS({SHEET1}, HAS(@cell, $[Primary Column]@row), {SHEET2}, [Column1]$17)

  • Laura G
    Laura G ✭✭
    Options

    Hi there

    This returns a value but not the correct one.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Can you share a screenshot of the sheet with the data in and the result you get? (you can obscure names if needed, just add something to identify one or two in both sheets).