Lookup two columns in the same sheet and return unique values and counts

jhank

I have a sheet where issues are logged. The primary is a record ID, I have another column "Assigned" which has 7 names in a dropdown. Another column is "Field" with 9 options in a dropdown. I am trying to create a formula that will lookup unique pairs from Assigned and Filed and count the number of records for that pair that I can reference for a dashboard chart. The reason is that when new employees are added we do not want to to go back and create 9 more status and formula lookup formulas. We would like to avoid manually creating these rules for each pair as well if possible. Any ideas would be greatly appreciated. Thank you

Andrée Starå

Hi,

I would recommend using the JOIN function to get the unique pairs and then count those.

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jhank

Thank you, I dont believe the JOIN function will work in the Name column these repeat as well as the field columns. In the end, I am looking to make a list of the unique pairs and count them not have a 1-1 for the fields. Attached is the summary of the columns. I believe we could set them up manually for each "Criteria" However, once we have a dashboard, we want this sheet automated so that if a new employee is added to the tracking sheet it will automatically update rather than us having to go back in and maintain criteria formulas.

jhank

0

 

Thank you, I dont believe the JOIN function will work in the Name column these repeat as well as the field columns. In the end, I am looking to make a list of the unique pairs and count them not have a 1-1 for the fields. Attached is the summary of the columns. I believe we could set them up manually for each "Criteria" However, once we have a dashboard, we want this sheet automated so that if a new employee is added to the tracking sheet it will automatically update rather than us having to go back in and maintain criteria formulas.

Eric M Oliveira

Hello,

 

Happy to help! If you'd like to count the number of records for each pair you can achieve this utilizing a COUNTIFS. I would recommend utilizing a cross-sheet COUNTIFS where all of the formulas can be contained. 

 

On the sheet you'll create 4 columns, "Field", "Assigned", record ID,  & a COUNT Formula Column. Having these four columns will give you the ability to not need to hard code each name.

 

In the Record ID you'll have to manually type the desired Record ID this will be the only value you will not be able to automate.

 

In both the Field and Assigned columns you can utilize separate cross-sheet VLOOKUP formulas to reference the respective columns on the master sheet based on the Record ID. Meaning in one column "Field" it will have a cross-sheet VLOOKUP pulling into the sheet only the Field data from the master sheet based on the Record ID. In the Assigned column it will have a cross-sheet VLOOKUP pulling into the sheet only the Field data from the master sheet based on the Record ID.

 

Then in the COUNT Formula Column, you can utilize a cross-sheet COUNTIFS formula to reference the Field and the Assigned values, produced by the VLOOKUPS, to count the amount of Record ID's on the master sheet that matches the pair criteria.

 

This Help Center article outlines how to utilize a VLOOKUP Formula: https://help.smartsheet.com/function/vlookup

 

This Help Center article outlines how to utilize a COUNTIFS Formula: https://help.smartsheet.com/function/countifs

 

This Help Center article explains in further detail cross-sheet formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/advanced-cross-sheet-formulas

 

Have a wonderful day,

Eric  -  Smartsheet Technical Support

Andrée Starå

Happy to help!

I saw that Eric answered!

Let me know if I can help with anything else!

Best,

Andrée

jhank

Thank you this was very helpful.