Counting number of adjacent cells

[BuddJ]

Hia,

I am looking for a formula that will count the number of adjacent cells that meet a specific condition starting from Column A & looking for the word Required.

In the example below ......
Row 1 should return 2
Row 2 should return 1
Row 3 should return 2
Row 4 should return 3
Row 5 should return 0
Row 6 should return 0

Required	Required	N#A
Required	Match (12-JUN-18)	Required
Required	Required
Required	Required	Required
Not Required	Required	Required
Match (12-JUN-18)	Required	Match (12-JUN-18)

<tbody>
</tbody>

Hope the above makes sense - have been bashing my head on this one for hours now so hoping someone can point me in the right direction.

Many thanks !!

 

[Special-K99]

You need to define "a number of adjacent cells"

I have a solution based on your three columns but you're implying there may be more than three in whcih my solution won't work

 

[BuddJ]

You need to define "a number of adjacent cells"

I have a solution based on your three columns but you're implying there may be more than three in whcih my solution won't work

There are a total of 7 columns in the range that I need to evaluate.

 

[Special-K99]

Given now that there are 7 columns what is the expected result if you have Required in columns

A B D E F

A and B are adjacent that would return 2 in your original example
but now there are 7 columns so should the result be 3 (D E F)
Hence the need to ask how many columns there would be.

So the question is are you looking for the maximum number of adjacent cells in a row that contain "Required"?

 

[Special-K99]

Given now that there are 7 columns what is the expected result if you have Required in columns

A B D E F

A and B are adjacent that would return 2 in your original example
but now there are 7 columns so should the result be 3 (D E F)
Hence the need to ask how many columns there would be.

So the question is are you looking for the maximum number of adjacent cells in a row that contain "Required"?

 

[BuddJ]

For rows that start with the word "Required" in the first Column, the formula needs to count how many cells to the right have the word "Required" in them as long as they are adjacent to each other. As soon as a cell contains any word apart from "Required" then the count should stop & ignore the remainder of the row.

In the original example irrespective of what data is in in columns D, E, F for row 1 the count would still be 2 as the N#A would stop the formula.

 

[BuddJ]

Thanks - have found the answer