Excel Count if first character is 0 in string

Question

I am looking for an Excel function that will check down a column and look for cells where the first character is a 0.

In the case of the below example, you can see that there are a total of 3 cells in the below column that have a 0 as the first character. I want to simply get a count of these for the column. I thought of using a COUNTIF formula but I wasn't sure how to make sure it's only checking the first character, as there could be a possibility of 0's in other places (i.e. 10/10, 5/100, etc..)

Answer 1

You could try using one of the following, assume you are using MS365:

---

• Using TEXTBEFORE() function:

=SUM(N(IFNA(--TEXTBEFORE(B2:B21,"/")=0,0)))

---

• Or, Using COUNTIF() or COUNTIFS() with wild card operator :

=COUNTIF(B:B,"0/*")

---

If you are insisting for a dynamic approach something like this you could try as well:

---

=COUNTIF(B2:INDEX(B:B,MATCH(2,1/(B:B<>""))),"0/*")

---