Countif maybe or Lookup??

[mpenkala]

Hey,

I'm looking for some help with the following problem. In ColumnA, from row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for a
formula that will scan through the list of numbers in ColA and find all the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

 

[Ron Coderre]

Try this:

=SUMPRODUCT((A1:A20=1)*(A2:A21=1))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Don Guillett]

How about a nice macro
Sub ifonefollowedbyone()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "a") = 1 And Cells(i - 1, "a") = 1 Then _
MsgBox Cells(i, "a").Address
Next i
End Sub

 

[Bob Umlas, Excel MVP]

=SUMPRODUCT(--(A3:A199=1),--(A4:A200=1))
--this will tell how many pairs of 1's there are in A3:A200

 

[mpenkala]

Hi Ron,

This worked great except for the 0 pairs. When I enter
=SUMPRODUCT(($A$3:$A$999=0)*($A$4:$A$1000=0))
It counts all 0's followed by 0's like it should, but also counts all blank
cells followed by another blank cell. Any way around this? Or do I just
adjust my range?

Thanks,
Matt

 

[Ron Coderre]

Hmmm....You only mentioned that you wanted to count pairs of 1's.

For consecutive zeros, try this:

=SUMPRODUCT(($A$3:$A$999<>"")*($A$3:$A$999=0)*($A$4:$A$1000=0)*($A$4:$A$1000<>""))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[mpenkala]

Perfect Ron, works great.

Thanks a bunch.
Matt

Hmmm....You only mentioned that you wanted to count pairs of 1's.

For consecutive zeros, try this:

=SUMPRODUCT(($A$3:$A$999<>"")*($A$3:$A$999=0)*($A$4:$A$1000=0)*($A$4:$A$1000<>""))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[ShaneDevenshire]

Hi mpenkala,

If you were just trying to find them you could use a conditional format or
create an adjacent column with a formula to return TRUE and FALSE next to
each.

The formula for both cases would be something like:

=AND(OR(A1=A2,A2=A3),A2=$C$1)

Where the entry in C1 is the value you want to find if repeated in adjacent
cells.