Count If a specific department answered "Yes" at least once

Briana Caterino

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

Paul Newcome

@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.

Andrée Starå

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

Paul Newcome

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)

Andrée Starå

@Briana Caterino,

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Briana Caterino

@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!

Paul Newcome

@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.

Briana Caterino

@Paul Newcome Thank you, thank you, thank you!!!

Paul Newcome

Happy to help! 👍️