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

RGarrels

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

@RGarrels

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

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