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?


Try this:


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


The FILTER function has a way with it as well:




enter image description here


I've created an example file: how to use LEFT function in COUNTIF

  • I don't follow the notation A2:A. Does that mean "start at A2 and include the rest of column A" ? – patrickgamer Nov 18 '12 at 18:16
  • Indeed, else the header will be included as well. Have you seen my example file? – Jacob Jan Tuinstra Nov 18 '12 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.