count how many times we have the same value in two (or more) adjacent cells

Guys,

I am trying to figure out a way to calculate the Bradford factor - https://en.wikipedia.org/wiki/Bradford_Factor.
It's =the total number of spells/instances/occurrences of absence squared, multiplied by the total number of days of absence.
And for this, I need to come up with a way to count the occurrences of a certain data (in my case - SL's) in adjacent cells (the sum of the absent days is a simple sumif or countif).
To clarify:

Basically in this example we have a total of 7 SL (Sick Leave) days in 2 occurrences.
screen1.JPG

So I am scratching my head trying to figure out how I can calculate the "occurrences", e.g. how many times we have the "SL" occurring in 2 or more adjacent cells.

I am trying to figure out how to count the "2 times".

Any ideas guys?

Try this formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi,

Try attached one.

=SUMPRODUCT((B2:AC2="sl")*(A2:AB2<>"sl"))

If this was the data...

Data Range

	A	B	C	D	E	F	G	H
1	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan
2	SL		SL	SL				SL

Your formula returns a result of 2.

This array formula** returns a result of 1:

=SUM(IF(FREQUENCY(IF(A2:H2="SL",COLUMN(A2:H2)),IF(A2:H2<>"SL",COLUMN(A2:H2)))>=2,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You're welcome. Thanks for the feedback!