Need to Count Occurences in Multiple Ranges

[Guest]

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.

 

[T. Valko]

Try this:

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

 

[PCLIVE]

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(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),"P","")))+(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),"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","")))

 

[Guest]

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