Google Sheets, COUNTIF single or double characters (eg, count "I" and "II" separately)

Question

I have a list of violin players. They are ordered like this:

I 01
I 02
I 03
II 01
II 02
II 03

I want to count how many "I" and "II" are there, but using COUNTIF(range, "I*") it counts both the I and the II instances. How do I solve this?

Answer 1

Try

=QUERY(ARRAYFORMULA(IF(A1:A="",,REGEXEXTRACT(A1:A,"\w+"))),"select Col1,count(Col1) where Col1 is not null group by Col1 ")

how to extract word characters

Answer 2

For something a little less elegant than Mike's solution, but perhaps simpler, try:

=countif(arrayformula(split(A1:A6," ")),"I") for counting Is

=countif(arrayformula(split(A1:A6," ")),"II") for counting IIs

---

Briefly, split(A1:A6, " ") splits the data by the blank and creates a two-column-six-row array with the Is and IIs in the first column.

And then countif() counts the Is and IIs.