Master Metric sheet. Multiple users to 1 row

Laura G

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.

KPH

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

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

Hi @Laura G

Try

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

Laura G

Hi there

This returns a value but not the correct one.

KPH

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).