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

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

Find more posts tagged with

Construction

Sheets

Comments

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.