Formula Help  Counting Multiples as 1
Hello  Need some help figuring out the correct formula to use for what I need. I've put a sample data table below. Essentially, let's say I have a sheet of requests that I need to to track by category. 1 category may have several rows but I only need to count 1 for any of that category that have an unchecked box. See data table below for this to make a bit more sense (made up data for confidentiality reasons)
In this sample, This would be the output I was looking for:
Category 1: 2
Category 2: 1
Essentially if looking by project and category, if all the checkboxes are checked, then it is 0. If any are unchecked it is 1.
Hope this makes sense and looking forward to someone's input!
Best Answer

Hi @Kelly Pratt
I would use a COUNT(DISTINCT(COLLECT formula here!
It's essentially counting the distinct Project values based on the Category selected and if a box is unchecked for that category & project.
For example, if you're using an insheet formula:
=COUNT(DISTINCT(COLLECT(Project:Project, Category:Category, Category@row, [Complete?]:[Complete?], 0)))
If you're using a crosssheet formula:
=COUNT(DISTINCT(COLLECT({Project Column}, {Category Column}, Category@row, {Complete Column}, 0)))
Let me know if that makes sense and works for you, or if you'd like to see screen capture examples!
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Answers

Hi @Kelly Pratt
I would use a COUNT(DISTINCT(COLLECT formula here!
It's essentially counting the distinct Project values based on the Category selected and if a box is unchecked for that category & project.
For example, if you're using an insheet formula:
=COUNT(DISTINCT(COLLECT(Project:Project, Category:Category, Category@row, [Complete?]:[Complete?], 0)))
If you're using a crosssheet formula:
=COUNT(DISTINCT(COLLECT({Project Column}, {Category Column}, Category@row, {Complete Column}, 0)))
Let me know if that makes sense and works for you, or if you'd like to see screen capture examples!
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Thank you, @Genevieve P.  never circled up to let you know that this worked!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 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