Counting adjacent cells?

[Miltos01]

Hi there!!!

I've got the following situation and need help:

In column of an Excel spreedsheet -let's say coloumn A- I have the following:

Column A
Blanck Cell
Blanck Cell
1
1
Blanck Cell
Blanck Cell
1
1
Blanck Cell
1
1
1
Blanck Cell
Blanck Cell
...
1
Blanck Cell etc...

I want to count the number of the 1's occuring 1time, 2times,3 times etc consecutively. Based on the given example the number of 2 adjacent 1's is 2 because 2 adjacent 1's appears 2 times.

Thank you...

[davesexcel]

Maybe check out the areas function.

Here is something I did using VBA years ago, I didn't use the count function though.
http://www.davesexcel.com/vbacodes.htm#858291122

[FDibbins]

Assuming your data starts on A2, down, try this. In B2, copied down...
=IF(A2="","",IF(A2=A1,SUM(B1,1),1))
Then create a table something like this...

	E	F
2	Number	Count
3	1	1
4	2	2
5	3	2
6	4	1
7	5	0

In F3, copied down...
=COUNTIF(B:B,E3)-COUNTIF(B:B,E4)

[RobertMika]

=SUMPRODUCT(--((FREQUENCY(IF(A2:A20=C2,ROW(A2:A20)),IF(A2:A20<>C2,ROW(A2:A20))))=D2))
frequency.xlsx
Confirm COntrl+shift+Enter

[Miltos01]

Thanks, the last solution with sumproduct worked well. I was trying to figure out why it works.... Can you explain to us RobertMika why? (if it doesn;t bother you give us a few details). What about if I have the mark "X" instead of blank cells between data? How can I use then your formula?

[Tony Valko]

Since the formula has to be array entered** you may as well just use SUM:

=SUM(IF(FREQUENCY(IF(A2:A20=C2,ROW(A2:A20)),IF(A2:A20<>C2,ROW(A2:A20)))=D2,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.

[Tony Valko]

What about if I have the mark "X" instead of blank cells between data? How can I use then your formula?

Since you're counting consecutive cells that contain the number 1, X's instead of empty cells won't make a difference.


Data Range

	A	B	C	D
1
2	X		1	1
3	X		2	2
4	1		3	1
5	1		4	0
6	X
7	X
8	1
9	1
10	X
11	1
12	1
13	1
14	X
15	X
16	X
17	1
18	X
19
20

This array formula** entered in D2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$20=1,ROW(A$2:A$20)),IF(A$2:A$20<>1,ROW(A$2:A$20)))=C2,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.

[Miltos01]

Tony Valko, thanks again for your help... It's perfectly understood!!! The formula works like a charm...

What about if I have the following situation:

Shorting.jpg

How you hand these?

Thanks again for your replies....

[Tony Valko]

Tony Valko, thanks again for your help... It's perfectly understood!!! The formula works like a charm...

Good deal!

What about if I have the following situation:

Attachment 267264

How you hand these?

Thanks again for your replies....

I don't understand what you want with that?

[Miltos01]

After multiple occurances of number draws some patterns arise...and I want to study those patterns as far as their shape...The shape of a pattern is a trend. Maybe those patterns in some cases can be valuable for understanding formation and in some cases predict results!

Thanks again...