Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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