Need to Count Unique Values Based on Criteria from a DropDown Collumn

Mashall Greene

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!

Paul Newcome

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?

Mashall Greene

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.

Paul Newcome

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)

Mashall Greene

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.

Paul Newcome

Try something like this...

=COUNTIFS(Dropdown:Dropdown, CONTAINS("Yes", @cell), Helper:Helper, @cell <> 1) * 2 + COUNTIFS(Dropdown:Dropdown, CONTAINS("No", @cell))

Mashall Greene

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

Paul Newcome

I have responded there.