How to use Countifs for rows that start with a certain character?

I am trying to use countifs formula. I want it to return how many there are that meet two criteria- the first being the first digit of a three digit number in one column is 1--. It would also have to be "Completed" in another column.

=COUNTIFS({Number}, HAS(LEFT(@cell, 1)= "1:"), {Status}, "Completed")



  • Mike TV
    Mike TV ✭✭✭✭✭✭


    I couldn't get this working using HAS but I was able to get it working using CONTAINS:

    =COUNTIFS(Number:Number, CONTAINS("1", LEFT(@cell, 1)), Status:Status, "Completed")

    I'm not sure what the colon is about in your formula, "1:", so maybe you need to use this instead:

    =COUNTIFS(Number:Number, CONTAINS("1:", LEFT(@cell, 1)), Status:Status, "Completed")

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    Oh ya, you'll need to substitute Number:Number and Status:Status with your cross-sheet references.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!