Forum Discussion
Create a unique list of names based on multiple criteria
Hi there! I currently have a master list that has names, organizations and whether or not they attended a workshop. I need to create a list for each agency that lists the name of the individuals, but only when they have attended a workshop. I tried to create the list using the formula based off this page: https://exceljet.net/formula/unique-values-with-multiple-criteria but I keep getting a #calc error message. The formula works if I only list one criteria though. Thoughts? Would an if formula work better?
Thank you! The values in columns C and D are numbers, not text values, so you shouldn't use quotes around values such as 1. The formula in M4 should be
=UNIQUE(FILTER($A$2:$A$5,($B$2:$B$5=M3)*($C$2:$C$5=1)))
This can be filled to the right to N4. Note that I made the references to A2:A5, B2:B5 and C2:C5 absolute so that they don't change when you fill to the right.
5 Replies
Could you attach a small sample workbook (without sensitive information)?
- HansVogelaar I just updated the post with a sample excel file so you can see what type of a document I was working with and how I set up the formula as well.
Thank you! The values in columns C and D are numbers, not text values, so you shouldn't use quotes around values such as 1. The formula in M4 should be
=UNIQUE(FILTER($A$2:$A$5,($B$2:$B$5=M3)*($C$2:$C$5=1)))
This can be filled to the right to N4. Note that I made the references to A2:A5, B2:B5 and C2:C5 absolute so that they don't change when you fill to the right.
