Need to Count Unique Values Based on Criteria from a DropDown Collumn
Hello,
I need a function that can identity duplicates and determine if they should be counted as one or more than one depending on criteria within another column of the spreadsheet that is a dropdown.
The specific context is there is a Name Column (Column A) and a Dropdown Column (Column B), Column B has three options "Yes - the Same", "Yes - Different", and "No".
"Yes - the Same" should be counted as one
"Yes - Different" should count all the duplicates in the name column
"No" - should count all the duplicates in the name column
I have tried putting multiple functions together using the Distinct, IF, COUNTIFS, AND commands but have had no luck so far.
This function will live in the sheet summary tab and will referenced by other simpler functions to track things.
Please let me know if there are any questions and I would really appreciate it if somebody could help me out here.
Thanks!
Answers
-
You are going to want to add in a "helper" column that will flag the rows to be counted.
How are new rows entered into the sheet?
-
Hi Paul,
Thank you for the reply, new rows are added to the sheet through a form. I'm happy to manually update the fields of the spreadsheet, but the range of the function should be the entire name and entire dropdown column.
-
Ok. I would suggest inserting a checkbox column and placing a formula similar to below into row 1 and then dragfilling down to cover the rest of the rows. Autofill should pick up any new rows as they are added after setting this up.
=IF(COUNTIFS(Name$1:Name@row, Name@row, Dropdown$1:Dropdown@row, "Yes - the Same") > 1, 1)
Then you would include this "helper" column in your original COUNTIFS to exclude rows where the boxes are checked.
=COUNTIFS(..........................................., Helper:Helper, @cell <> 1)
-
Thanks Paul, follow-up question.
How can I assign an option of a cell to be counted for twice instead of once and a great COUNTIFs function? In context, I'd like for my answers as "Yes" to be counted as two and "No" to be counted as one.
-
Try something like this...
=COUNTIFS(Dropdown:Dropdown, CONTAINS("Yes", @cell), Helper:Helper, @cell <> 1) * 2 + COUNTIFS(Dropdown:Dropdown, CONTAINS("No", @cell))
-
Thanks Paul,
This was helpful, but I have opened a separate question to discuss this in more detail, can you please take a look at this link?
https://community.smartsheet.com/discussion/68880/complex-countifs-and-if-function#latest
-
I have responded there.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!