How can I add up how often certain text occurs?
 

Hi everyone.

My problem is to add up how often certain text occurs in a column. Doing
this when the cells I want to use are sequential eg B2 - B12, is easy. I use
=COUNTIF(B2:B12,"E").
What I can't do is add up when the cells aren't sequential eg B2 - B5, B7,
B10 - B12.
I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too
many arguements. Using colons and commas in this way works when I add
numbers, but not when I add occurences of text, and I don't know why!

Any help would be gratefully recieved.

Many thanks, Martin

Is there a reason why you can use the cells sequentially? Unless the cells
you're omitting contain the result you're looking for, it shouldn't count
them right, or am I missing something?

"Martin M" wrote:

Hi everyone.

My problem is to add up how often certain text occurs in a column. Doing
this when the cells I want to use are sequential eg B2 - B12, is easy. I use
=COUNTIF(B2:B12,"E").
What I can't do is add up when the cells aren't sequential eg B2 - B5, B7,
B10 - B12.
I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too
many arguements. Using colons and commas in this way works when I add
numbers, but not when I add occurences of text, and I don't know why!

Any help would be gratefully recieved.

Many thanks, Martin

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(B2:B12),{2,3,4,5,7,10,11,12},0 ))),--(B2
:B12="E"))

OR

=SUMPRODUCT(COUNTIF(INDIRECT({"B2:B5","B7","B10:B1 2"}),"E"))

Hope this helps!

In article ,
"Martin M" wrote:

Hi everyone.

My problem is to add up how often certain text occurs in a column. Doing
this when the cells I want to use are sequential eg B2 - B12, is easy. I use
=COUNTIF(B2:B12,"E").
What I can't do is add up when the cells aren't sequential eg B2 - B5, B7,
B10 - B12.
I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too
many arguements. Using colons and commas in this way works when I add
numbers, but not when I add occurences of text, and I don't know why!

Any help would be gratefully recieved.

Many thanks, Martin

Hello Martin

You already have the answer using =COUNTIF(A1:A12,"E") will add only
E's even if you have other letters within the range.

Hope this helps


--
robmeister
------------------------------------------------------------------------
robmeister's Profile: http://www.excelforum.com/member.php...o&userid=24364
View this thread: http://www.excelforum.com/showthread...hreadid=380158