Counting adjacent cells containing specific value

[colin5392]

I have a "x" in A2 and "x" in A3, again in A17/A18, again in A200/A201, etc.

How do I count the number of instances throughout the column of cells with adjacent (down) values?

And how would I do this for the entire dataset, which expands across from column A to column BT?

Can this be done without a macro?

 

[Special-K99]

Not saying I'll answer this but you havent specified things clearly.

Your examples are ALL 2 adjacent cells so

1. Are there ever 3 or more cells adjacent?
2. If so, if A300/A301/A302 all contained x how would you count that? One occurrence of two or more adjacent cells containing x, or are you counting each occurrence of two cells next to each other so the result would be 2 A300/A301 and A301/A302 ?
3. Where should this data be stored? In each column would be preferable but you havent said how far down the x's go, so maybe the results should go on another sheet?

 

[colin5392]

Apologies:

1. Yes, the maximum seen thus far is 4
2. Count only once no matter the size - would have to determine empty cells in between instances
2a. Is there a way of counting blanks cells between each instance?
3. Data would go on another sheet once established how this can be done

 

[Special-K99]

2a was my thought too after reading the answer to 2.

Not sure, will have to have a think about it.

 

[colin5392]

Thanks, in advance

 

[Usercode]

I am also interested in the solution of this question.

 

[Rick Rothstein]

I am also interested in the solution of this question.

I think this array-entered** formula should work...

=SUM(--(A1:BS1&B1:BT1&"|"="x|"))/2

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Usercode]

I am sorry, Rick, I didn't understand your formula. Lets say I have values like this from A to H. And I want to count instances of adjacent "x"s if there are two, three or more repetitions in the same column. How would I count this in such a scenario for each column instead of the whole table?

		x	x		x	x	
		x			x	x	x
			x	x	x	x	x
x	x				x	x	x
x	x	x		x	x		
			x		x	x	x
		x	x			x	x
x	x	x	x			x	
x	x			x	x		x
x		x		x		x	
			x	x		x	
		x	x	x	x	x	
		x	x	x	x		
		x			x	x	x
x	x	x	x	x	x		
	x	x	x			x	x
	x		x	x	x	x	x
x			x		x	x	
x		x		x		x	x
x					x	x	x
	x		x	x		x	x
x		x		x		x	x
x			x	x	x	x	x
	x	x		x		x	x
x	x	x		x	x		x
x		x	x		x		x
x			x	x	x	x	
	x		x	x			
			x	x		x	x
x	x	x	x	x	x		
x	x			x	x	x	
x			x		x	x	x
x			x		x	x	x

 

[Rick Rothstein]

I am sorry, Rick, I didn't understand your formula. Lets say I have values like this from A to H. And I want to count instances of adjacent "x"s if there are two, three or more repetitions in the same column. How would I count this in such a scenario for each column instead of the whole table?

I am sorry... I misunderstood the request was for counting across a single row. For counting down a single column, put this array-entered** formula in a cell two or more rows below the last row of table data (here I am assuming the last row of table data is located on Row 33, so put this array-entered** formula in cell A35 or below)...

=SUM(--(A1:A33&IF(A2:A34="","|","")="x|"))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Usercode]

Thanks a lot, Rick. It works perfectly. I didn't start this thread but I found a solution, thanks again.