COUNTIF and COLLECT

If the column "Graduation Year" is "2021", I want to collect from the column "Position TYPE Post GME Training" the total number of cells with "Private Clinical Practice-local". See below. What am I doing wrong?🤔 There are multiple values in addition to "Private Clinical Practice-local" in the dropdown for "Position TYPE Post GME Training" and I'll need to collect a total for each value. Appreciate your help.

=COUNTIF([Graduation Year]:[Graduation Year](COLLECT([Position TYPE Post GME Training]:[Position TYPE Post GME Training], @cell=Private Clinical Practice local,"2021"))

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/14/21 Answer ✓

    Hi Barbara,

    Does this get you what you're looking for?

    =COUNTIFS([Graduation Year]:[Graduation Year], 2021, [Position TYPE Post GME Training]:[Position TYPE Post GME Training], HAS(@cell, "Private Clinical Practice local"))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/14/21 Answer ✓

    Hi Barbara,

    Does this get you what you're looking for?

    =COUNTIFS([Graduation Year]:[Graduation Year], 2021, [Position TYPE Post GME Training]:[Position TYPE Post GME Training], HAS(@cell, "Private Clinical Practice local"))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you Mark! It worked! Last night I figured out this worked as well. (see below).

    COUNTIFS([Graduation Year]:[Graduation Year], "2021", [Position TYPE Post GME Training]:[Position TYPE Post GME Training], @cell="Private Clinical Practice local")

    Barbara

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Excellent. Glad you found an answer. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!