How to use LEFT function in COUNTIF

Question

I have a column that sometimes has B and sometimes B - somecomment. I want to count all instances of B. I expected it to be something like

=COUNTIF(B14:B943, LEFT(B14:B943,1) = "B")

But no dice. What am I missing? Is there a better way to solve this problem?

Answer 1

Try this:

=countif(arrayformula(left($A$30:$A$38)),"B")

What you're doing is trying to sum the relevant values before actually running the left() function.

Using arrayformula() will let you evaluate the function over the range and use its output as a single number (basically allows you to run the left() prior to summing the count of "B")

More about using arrayformula(): http://support.google.com/drive/bin/answer.py?hl=en&answer=71291

Answer 2

The FILTER function has a way with it as well:

=COUNTA(FILTER(A2:A;LEFT(A2:A;1)="B"))

See also small example I've prepared: How to use LEFT function in COUNTIF