Counting Question

[Maverick_777]

Good afternoon all,
I have a question on a counting function.

I can devise a "countif" function to count the number of times a cell has the letter "L" in it with no problem. For the example assume each "L" and "O" are in their own cells.
ex. L O L L O L

The total count of "L"'s would be 4.

However for my question, I want the number to add to 3. Meaning if there were 2 "L"s next to each other or 20 in consecutive cells, I want it to count as One time. This is to account for an Occasion. Two or more in a row would be one occasion.
ex. In the above example, the first "L" would count as 1, the second and third "L" would count as 1 (because it was in two or more adjacent cells), and the 4th "L" would count as 1, for a total of 3 occasions.

If this makes any sense I would appreciate some ideas or thoughts.

Thanks in advance everyone...

 

[Colin Legg]

This may be an oversimplification, but supposing your data:

L O L L O L

is in cells A1:F1 then you could give this formula a try:

=SUMPRODUCT(--(A1:F1="L"),--(B1:G1<>"L"))

Does that seem to work for you?

 

[Gerald Higgins]

So it seems that you really want to count the number of times the data changes from O to L, yes ?
One simple way is to use a helper column or row.
For example, if your data is in the range B1:G1, put a formula like this in B2, and copy through to G2.

=if(B1="L",if(A1="L",0,1),0)

Then a sum of B2:G2 should give you a count of the changes.

 

[Maverick_777]

Thank you for your suggestions Colin and Gerald. Both seemed to work out well, but I will be using it on a summary sheet and maybe inconjunction with conditional formatting.

That being said, I think in this instance Colin's suggestion would probably work best.

I have these issues from time to time so I'll log both suggestions in my Excel help file for later use.

Thanks again for the help guys.