Thank you for pointing that out

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+1

That doesn't work. Try it with this data:

0;1;1;1;0;1;1;0;1;1

Also, what is the *actual* range in that formula? You're referencing 1
cell above or 1 cell below the *actual* range. What if there's similar
other non-related data in those cells?

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Try this

=SUMPRODUCT((D5:D14=1)*(D5:D14=D4:D13))+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"stuart" wrote in message
...
hi everyone and all you super intelligent people out there
(thought id start with a compliment to make you smile)

anyway here is what im after from the below data i want to be able to
see if
a person has more than 3 occurances of the number 1 in a row or not - i
can
easily count the total 1's however i want to be able to see if more than
3
occur in sequence - any suggestions

column A
row person flag
1 1
2 0
3 0
4 1
5 1
6 1
7 1
8 0
9 0
10 1
="little formula to tell me yes 1 appeared it happend 4 times in
row"

hope you can help

thanks in advance

me