Need to Count Occurences in Multiple Ranges
 

I have two non-contiguous ranges that contain individual letters. I need a
formula that will count the number of occurrences of the letters P and L
within those ranges, let's call them B7:Q7 and B18:E18. Can it be done with
one formula or do I have to count the ranges separately and then add them
together?

I'm running Excel 2003. Thanks in advance for any help.

Need to Count Occurences in Multiple Ranges
 

Try this:

=SUM(COUNTIF(B7:Q7,{"P","L"}),COUNTIF(B18:E18,{"P" ,"L"}))

--
Biff
Microsoft Excel MVP


"Loris" wrote in message
...
I have two non-contiguous ranges that contain individual letters. I need a
formula that will count the number of occurrences of the letters P and L
within those ranges, let's call them B7:Q7 and B18:E18. Can it be done
with
one formula or do I have to count the ranges separately and then add them
together?

I'm running Excel 2003. Thanks in advance for any help.

Need to Count Occurences in Multiple Ranges
 

If your cells will not have additional characters in them (meaning, only "P"
or only "L" with no other characters in the cells with them, then see
T.Valko's response.
If the cells have more than just "P" or "L" (example: "Paul" or "Larry" one
count as 1 each), then here's the hard way:


=(LEN(CONCATENATE(B7,C7,D7,E7,F7,G7,H7,I7,J7,K7,L7 ,M7,N7,O7,P7,Q7))-LEN(SUBSTITUTE(CONCATENATE(B7,C7,D7,E7,F7,G7,H7,I7 ,J7,K7,L7,M7,N7,O7,P7,Q7),"L","")))+(LEN(CONCATENA TE(B7,C7,D7,E7,F7,G7,H7,I7,J7,K7,L7,M7,N7,O7,P7,Q7 ))-LEN(SUBSTITUTE(CONCATENATE(B7,C7,D7,E7,F7,G7,H7,I7 ,J7,K7,L7,M7,N7,O7,P7,Q7),"P","")))+(LEN(CONCATENA TE(B18,C18,D18,E18,F18,G18,H18,I18,J18,K18,L18,M18 ,N18,O18,P18,Q18))-LEN(SUBSTITUTE(CONCATENATE(B18,C18,D18,E18,F18,G18 ,H18,I18,J18,K18,L18,M18,N18,O18,P18,Q18),"L","")) )+(LEN(CONCATENATE(B18,C18,D18,E18,F18,G18,H18,I18 ,J18,K18,L18,M18,N18,O18,P18,Q18))-LEN(SUBSTITUTE(CONCATENATE(B18,C18,D18,E18,F18,G18 ,H18,I18,J18,K18,L18,M18,N18,O18,P18,Q18),"P","")) )


"Loris" wrote in message
...
I have two non-contiguous ranges that contain individual letters. I need a
formula that will count the number of occurrences of the letters P and L
within those ranges, let's call them B7:Q7 and B18:E18. Can it be done
with
one formula or do I have to count the ranges separately and then add them
together?

I'm running Excel 2003. Thanks in advance for any help.

Need to Count Occurences in Multiple Ranges
 

I think you will have to use four functions
=countif(range1,"P")+countif(range1,"L")+countif(r ange 2.....

"Loris" wrote:

I have two non-contiguous ranges that contain individual letters. I need a
formula that will count the number of occurrences of the letters P and L
within those ranges, let's call them B7:Q7 and B18:E18. Can it be done with
one formula or do I have to count the ranges separately and then add them
together?

I'm running Excel 2003. Thanks in advance for any help.