Basic counting formula required

[lukevl]

Hi, new to forum, long time reader, firts time poster. Basic question if someone can help me please.
I have a column that has series of a number (I can change this) eg:

1 ___ 1
2 ___ 1
3 ___ 1
4
5
6
7 ___ 1
8 ___ 1
9
10
11
12 __ 1
13 __ 1
14 __ 1
15
16
17 __1

What I want is a counter that counts how many SETS of '1' there are. So for the example above the answer is '4'.

Can anyone help?

Thanks

[zbor]

In next column (for example C1) add =MAX(B1-B2;0) and extend down

Counting 1's now will give you number of sets.

[NBVC]

Try:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear.

[lukevl]

Try:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear.

Hey thanks a lot! works brilliantly. I don't think I would have figured that out for myself.

[DonkeyOte]

You could also perhaps use SUMPRODUCT ?

=SUMPRODUCT(--(A1:A17=1),--(A2:A18<>1))

[contaminated]

I cannot understand, what FREQUENCY does here?
please explain me someone...

[NBVC]

Try going through Formula Auditing and evaluate the formula to see how it gets evaluated.