# Counting Question

#### Maverick_777

##### Board Regular
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Colin Legg

##### MrExcel MVP
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:
Code:
``=SUMPRODUCT(--(A1:F1="L"),--(B1:G1<>"L"))``

Does that seem to work for you?

#### Gerald Higgins

##### Well-known Member
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.
Code:
``=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

##### Board Regular
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.

Replies
10
Views
490
Replies
1
Views
333
Replies
6
Views
469
Replies
13
Views
442
Replies
4
Views
451

1,191,697
Messages
5,988,165
Members
440,132
Latest member
Excellearner253

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back