Count If a specific department answered "Yes" at least once
Good Morning!
I've created a conflicts of interest survey and I'm looking for a formula to then reflect on my dashboard for reporting.
Can someone please help me identify how i can use a formula to count if someone from a particular department answered "Yes" at least once within 5 questions? My point is to count how many people per specific department have answered yes to at least one conflict within our five questions.
Any help would be so greatly appreciated. thank you!
Briana
Best Answer
-
@Briana Caterino My apologies. I missed the part where you wanted to specify a department. You would add that range/criteria to the final COUNTIFS formula.
=COUNTIFS([Helper Column]:[Helper Column], 1, [Department Column]:[Department Column], "Office of Compliance")
The above will count how many times the helper column is checked (at least one "yes" answer) where the department column contains the specified text.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hi Briana,
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The easiest way to go about this would be to add in a "helper" checkbox column that can later be hidden that will flag if any of the responses are "Yes".
=CONTAINS("Yes", [First Survey Response Column]@row:[Last Survey Response Column]@row)
Then you would use a COUNTIFS to see how many rows were checked.
=COUNTIFS([Helper Column]:[Helper Column], 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Paul Newcome Thank you! That helped clear up one of my formulas that I was working on!
@Andrée Starå Do you have an idea how I could count if a survey response from a particular department indicated yes at least once? Right now my survey has a user selecting their department from a drop-down and then selecting yes or no if there is a potential conflict of interest on 5 different questions. I am looking to count if someone from the "Office of Compliance" says "Yes" to at least once of the five potential times they could say yes.
Any idea how this could be achieved? I'm quite stuck. Thank you so so much!
-
@Briana Caterino My apologies. I missed the part where you wanted to specify a department. You would add that range/criteria to the final COUNTIFS formula.
=COUNTIFS([Helper Column]:[Helper Column], 1, [Department Column]:[Department Column], "Office of Compliance")
The above will count how many times the helper column is checked (at least one "yes" answer) where the department column contains the specified text.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Thank you, thank you, thank you!!!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!