Counting based on values from two columns while removing repeated entries
Hi,
I'm trying to count the number of entries based on two columns. In column A is the year, in column B are study numbers (eg AB1, TH2, etc) but some study numbers are repeated (eg TH2, TH2). I'm trying to count the number of unique entries in column B that contain TH that occur in a given year. I've tried this: =COUNTIFS([Column A]:[Column A], "2021", DISTINCT([Column B]:[Column B]), CONTAINS("TH", @cell)) but it's not working. I know I can do this by creating a helper column to only include rows in given year but ideally I'd like a single function to do this.
Thanks,
RLum
Best Answer

Hey @RLum
Try this
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], 2021, [Column B]:[Column B], CONTAINS("TH", @cell))))
Note I purposefully did not include quotes around 2021. 2021 is a number, not text.
If these are not your actual column names, be sure to edit this formula to make the column names match those in your sheet.
Will this work for you
Kelly
Answers

Hey @RLum
Try this
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], 2021, [Column B]:[Column B], CONTAINS("TH", @cell))))
Note I purposefully did not include quotes around 2021. 2021 is a number, not text.
If these are not your actual column names, be sure to edit this formula to make the column names match those in your sheet.
Will this work for you
Kelly

Thank you so much, this worked!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!