Attempting to create a formula to find items assigned to a contact from a drop down.

gwilkinson

I hope my title makes sense.

I work in a veterinary Setting, I am working to make some metrics for a sheet dashboard and I am trying to get a cell formula to show me how many of a category is assigned to a person. I have been able to figure out the totals per category in my sheet but now I want to be able to see it at the user level. This metric sheet references another master sheet for the totals.

Ultimately what I would love is a table that shows each of the categories from the drop down the contact is associated with.

The formula I am using to grab the totals per category: `

=COUNTIFS({Notice Type 1}, Category@row )

'

"Notice Type 1" is the reference to the drop down menu

Basically what I would like is how many notices Mister User received out of the 1274 total.

gwilkinson

I was able to figure this one out doing just that late last night.

=COUNTIFS({PI notification Range 3}, HAS(@cell , Category@row ), {PI Notification Range 1}, [Principle Investigator]2)

Using this I was able to make a small quick look form to put in any user and see what their stats were.

Now that I know the magic I plan to make another master sheet to show these stats across all vet techs and users. I have been wanting to leverage these metrics for a while and I finally have the bandwidth and proper formulas to help me along.

Thanks!

Jgorsich

Countifs() can accept multiple criteria.

=COUNTIFS({Notice Type 1}, Category@row,{assignd to},"Mister User" )

Adding the criteria for the person it is assigned to will limit the count to just the instances assigned to that user.

Corey W.

@gwilkinson You should be able to add a second range and criterion to your existing COUNTIFS() function to achieve this. Just reference the category range in your other sheet and use Testing@row as the criterion.

You could also put your dropdown with the users in the Sheet Summary so that you can select it once and update the counts for all categories based on that person.

gwilkinson

I was able to figure this one out doing just that late last night.

=COUNTIFS({PI notification Range 3}, HAS(@cell , Category@row ), {PI Notification Range 1}, [Principle Investigator]2)

Using this I was able to make a small quick look form to put in any user and see what their stats were.

Now that I know the magic I plan to make another master sheet to show these stats across all vet techs and users. I have been wanting to leverage these metrics for a while and I finally have the bandwidth and proper formulas to help me along.

Thanks!