Formula to tell me how many times a word shows up, but only for a specific Review Board

Here is what I need:

Count how many times Strongly Agree shows up, but only when the Review Board is General Medicine TAG.

I have this to give me the # of times Strongly Agree shows, how do I ensure I only get this for "General Medicine TAG": =COUNTIF({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree"))


Tags:

Best Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    The dirty way to do it is to add an IF:

    IF([Review Board]@row="General Medicine TAG",COUNTIF({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree")),"")

    Or you can use COUNTIFS and add [Review Board]@row, "General Medicine TAG" to your formula.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Leann Gibson

    It sounds like you only want to Count the "Strongly Agree" in the other sheet if the other sheet has "Oncology Tag" (or a different tag), is that correct?

    If so, you'll need to add in another criteria into your COUNTIF statement, making it COUNTIFS plural. I would even suggest you could take out the first IF and simply use the cell in [Review Board] as your value to search for:


    =COUNTIFS({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree"), {Column with Review Board in other sheet}, [Review Board]@row)


    See: COUNTIFS Function

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    The dirty way to do it is to add an IF:

    IF([Review Board]@row="General Medicine TAG",COUNTIF({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree")),"")

    Or you can use COUNTIFS and add [Review Board]@row, "General Medicine TAG" to your formula.

  • Leann Gibson
    Leann Gibson ✭✭✭✭✭✭

    @James Keuning It was working until I tried to use it for the other three groups. Am I doing something wrong? This one should come up "0" for all and it is pulling the numbers from the original one I did.


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Leann Gibson

    It sounds like you only want to Count the "Strongly Agree" in the other sheet if the other sheet has "Oncology Tag" (or a different tag), is that correct?

    If so, you'll need to add in another criteria into your COUNTIF statement, making it COUNTIFS plural. I would even suggest you could take out the first IF and simply use the cell in [Review Board] as your value to search for:


    =COUNTIFS({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree"), {Column with Review Board in other sheet}, [Review Board]@row)


    See: COUNTIFS Function

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Leann Gibson
    Leann Gibson ✭✭✭✭✭✭

    @Genevieve P. That worked - THANK YOU

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!