Countifs with Contains function

Desertbird
Desertbird ✭✭
in Formulas and Functions

Hello,

I am having trouble with this formula. Basically we have a list of employees with a column for their position, and their schedule. If they are in training, the schedule starts with "Effective" followed by the date they leave training. I am trying to pull the number of people in each position that are in training by locating the partial text. Below are two formulas I'm trying out but neither are working, any suggestions?


=COUNTIFS({Team Sheet Range 1}, Position@row, {Team Sheet Range 2},"Effective")


=COUNTIFS({Team Sheet Range 1}, Position@row, CONTAINS("Effective", {Team Sheet Range 2}))

· Share on FacebookShare on Twitter

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    =COUNTIFS({Team Sheet Range 1}, Position@row, {Team Sheet Range 2}, CONTAINS("Effective", @cell))

    Mark


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

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!