countifs + find formula with multiple criteria

edited 12/09/19 in Formulas and Functions


I'm fairly new to smartsheets and trying to solve a roadblock. 

I'm trying to count the amount of times a specific string is in a cell with two types of status: closed and submitted.

I'm using the FIND because some cells contain more than one ticket # (i.e. "Ticket-432, Ticket 12435")

=COUNTIFS({Database Range 1}, FIND("Ticket-12345", @cell) > 0) + =COUNTIFS({Database Range 2}, "Closed") + =COUNTIFS({Database Range 2}, "Submitted")

Much help is appreciated!



  • L_123
    L_123 ✭✭✭✭✭✭

    =COUNTIFS({Database Range 1}, contains("Ticket-12345", @cell)) + COUNTIFS({Database Range 2}, "Closed") + COUNTIFS({Database Range 2}, "Submitted")

    you don't need to start every function with an equals. only at the beginning. Contains is a much more friendly formula than find for text parsing

  • I appreciate the response! 

    I tried this out and the formula is valid, but it ends up counting the total of the ticket # + the # of Closed + # of submitted..resulting in a double/triple count than what the database actually reflects. Thoughts?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!