IF COUNTIF Formula help

System

This discussion was created from comments split from: check duplicate row and keep only the last one.

Paul Newcome

Getting the formula to only check a single box for each school involves a helper column. Auto-number (called "Auto" in this example) with no special formatting. Then the formula for the checkbox changes to:

=IF(COUNTIFS([High School]:[High School], @cell = [High School]@row, Auto:Auto, @cell <= Auto@row) = 1, 1)

Or in a sheet summary field or a separate sheet (updated with cross sheet references of course):

=COUNT(DISTINCT(COLLECT([High School]:[High School], [High School]:[High School], @cell <> "")))

Stacey Carrasco

Hi 🙋‍♀️@Paul Newcome

So I have this formula you helped me with

=IF(COUNTIF([High School]:[High School], [High School]@row) > 1, 0, 1)

It will check a box if the HS list is only listed once in the Range however I want the box to be checked if the school appears at least once.

I have schools that appear 3 times but the box won't check b/c it appears more then once. I want the box to check one box and not all 3 as long as the school appears.

I need a count of HS listed in our contact list and when I make a report from the sheet, the summarize option will count all HS when all I'm looking for is a total count of HS where it wont count the duplicates schools so I was thinking of using a check box option then summarizing the check box column. If you suggest another way to list the total count of HS please let me know.

Hope this makes sense 😄

Thanks

Paul Newcome

Getting the formula to only check a single box for each school involves a helper column. Auto-number (called "Auto" in this example) with no special formatting. Then the formula for the checkbox changes to:

=IF(COUNTIFS([High School]:[High School], @cell = [High School]@row, Auto:Auto, @cell <= Auto@row) = 1, 1)

Or in a sheet summary field or a separate sheet (updated with cross sheet references of course):

=COUNT(DISTINCT(COLLECT([High School]:[High School], [High School]:[High School], @cell <> "")))

Marcela

Hello @Stacey Carrasco!

Just wanted to @mention you to ensure you don't miss Paul's response.

Cheers

Marce

Stacey Carrasco

@Paul Newcome Yep the helper column and formula did the trick!!

Thanks!!

Paul Newcome

Happy to help. 👍️