cross referencing

I am trying to find a formula that looks at a range of cells, a2:b11, and if
a cell in the range has a value Phil and its adjacent cell in the other
column
has a value Tony then AA1=1, if no adjacent cells match the criteria AA1
=0. The names can be in either column.

For instance...

A B
Phil Tony would give AA1 value 1
Tony Phil would also give AA1 value 1
xxx yyy would give AA1 value 0

Thanks

Hi Phil
In cell AA1
=AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1

--
Regards

Roger Govier


"Phil" <someone@home.sometimes.uk> wrote in message
news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
>I am trying to find a formula that looks at a range of cells, a2:b11, and
>if
> a cell in the range has a value Phil and its adjacent cell in the other
> column
> has a value Tony then AA1=1, if no adjacent cells match the criteria AA1
> =0. The names can be in either column.
>
> For instance...
>
> A B
> Phil Tony would give AA1 value 1
> Tony Phil would also give AA1 value 1
> xxx yyy would give AA1 value 0
>
> Thanks
>
>
>
>
>

Thanks, that works if the values Phil and Tony are in A1 or B1 but they
could be anywhere from A2 : A11 or B2 : B11
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
> Hi Phil
> In cell AA1
> =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
>
> --
> Regards
>
> Roger Govier
>
>
> "Phil" <someone@home.sometimes.uk> wrote in message
> news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
> >I am trying to find a formula that looks at a range of cells, a2:b11, and
> >if
> > a cell in the range has a value Phil and its adjacent cell in the other
> > column
> > has a value Tony then AA1=1, if no adjacent cells match the criteria
AA1
> > =0. The names can be in either column.
> >
> > For instance...
> >
> > A B
> > Phil Tony would give AA1 value 1
> > Tony Phil would also give AA1 value 1
> > xxx yyy would give AA1 value 0
> >
> > Thanks
> >
> >
> >
> >
> >
>
>

What happens if you have *multiple* occurrences of "Tony" and "Phil"?

See if this works for you.

Will count *all* occurrences of "Tony" and "Phil" being on the same line
(row):

=SUMPRODUCT((A2:B11={"Tony","Phil"})*(A2:B11={"Phil","Tony"}))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Phil" <mrnw31666@blue.co.uk> wrote in message
news:XxMHe.81349$G8.48398@text.news.blueyonder.co.uk...
Thanks, that works if the values Phil and Tony are in A1 or B1 but they
could be anywhere from A2 : A11 or B2 : B11
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
> Hi Phil
> In cell AA1
> =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
>
> --
> Regards
>
> Roger Govier
>
>
> "Phil" <someone@home.sometimes.uk> wrote in message
> news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
> >I am trying to find a formula that looks at a range of cells, a2:b11, and
> >if
> > a cell in the range has a value Phil and its adjacent cell in the other
> > column
> > has a value Tony then AA1=1, if no adjacent cells match the criteria
AA1
> > =0. The names can be in either column.
> >
> > For instance...
> >
> > A B
> > Phil Tony would give AA1 value 1
> > Tony Phil would also give AA1 value 1
> > xxx yyy would give AA1 value 0
> >
> > Thanks
> >
> >
> >
> >
> >
>
>

Hi Phil

The following array formula works for me. To enter an array formula, press
Crontrol+Shift+Enter after the final ).
Do not enter the { } brackets, Excel will do this for you.
=IF(SUM((A1:A11="Phil")*(B1:B11="Tony")*1)+SUM((A1:A11="Tony")*(B1:B11="Phil")*1),1,0)

--
Regards

Roger Govier


"Phil" <mrnw31666@blue.co.uk> wrote in message
news:XxMHe.81349$G8.48398@text.news.blueyonder.co.uk...
> Thanks, that works if the values Phil and Tony are in A1 or B1 but they
> could be anywhere from A2 : A11 or B2 : B11
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
>> Hi Phil
>> In cell AA1
>> =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Phil" <someone@home.sometimes.uk> wrote in message
>> news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
>> >I am trying to find a formula that looks at a range of cells, a2:b11,
>> >and
>> >if
>> > a cell in the range has a value Phil and its adjacent cell in the other
>> > column
>> > has a value Tony then AA1=1, if no adjacent cells match the criteria
> AA1
>> > =0. The names can be in either column.
>> >
>> > For instance...
>> >
>> > A B
>> > Phil Tony would give AA1 value 1
>> > Tony Phil would also give AA1 value 1
>> > xxx yyy would give AA1 value 0
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>

Tony and Phil will only appear once
"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:OYdxqs3lFHA.2904@tk2msftngp13.phx.gbl...
> What happens if you have *multiple* occurrences of "Tony" and "Phil"?
>
> See if this works for you.
>
> Will count *all* occurrences of "Tony" and "Phil" being on the same line
> (row):
>
> =SUMPRODUCT((A2:B11={"Tony","Phil"})*(A2:B11={"Phil","Tony"}))
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Phil" <mrnw31666@blue.co.uk> wrote in message
> news:XxMHe.81349$G8.48398@text.news.blueyonder.co.uk...
> Thanks, that works if the values Phil and Tony are in A1 or B1 but they
> could be anywhere from A2 : A11 or B2 : B11
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
> > Hi Phil
> > In cell AA1
> > =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Phil" <someone@home.sometimes.uk> wrote in message
> > news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
> > >I am trying to find a formula that looks at a range of cells, a2:b11,
and
> > >if
> > > a cell in the range has a value Phil and its adjacent cell in the
other
> > > column
> > > has a value Tony then AA1=1, if no adjacent cells match the criteria
> AA1
> > > =0. The names can be in either column.
> > >
> > > For instance...
> > >
> > > A B
> > > Phil Tony would give AA1 value 1
> > > Tony Phil would also give AA1 value 1
> > > xxx yyy would give AA1 value 0
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
>

Thanks, that works OK. It a bit different to one i had , but stupidly lost
after a system crash, though. That , as far as i remember looked something
like....
=if(A2:B11=("Phil:Tony"),1,if(A2:b11=("Tony:Phil"),1,0)
I know thats not right cos it dont work.

Thank aagain
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:eZ1kFA4lFHA.3316@TK2MSFTNGP14.phx.gbl...
> Hi Phil
>
> The following array formula works for me. To enter an array formula, press
> Crontrol+Shift+Enter after the final ).
> Do not enter the { } brackets, Excel will do this for you.
>
=IF(SUM((A1:A11="Phil")*(B1:B11="Tony")*1)+SUM((A1:A11="Tony")*(B1:B11="Phil
")*1),1,0)
>
> --
> Regards
>
> Roger Govier
>
>
> "Phil" <mrnw31666@blue.co.uk> wrote in message
> news:XxMHe.81349$G8.48398@text.news.blueyonder.co.uk...
> > Thanks, that works if the values Phil and Tony are in A1 or B1 but they
> > could be anywhere from A2 : A11 or B2 : B11
> > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> > news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
> >> Hi Phil
> >> In cell AA1
> >> =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Phil" <someone@home.sometimes.uk> wrote in message
> >> news:o5MHe.81329$G8.58682@text.news.blueyonder.co.uk...
> >> >I am trying to find a formula that looks at a range of cells, a2:b11,
> >> >and
> >> >if
> >> > a cell in the range has a value Phil and its adjacent cell in the
other
> >> > column
> >> > has a value Tony then AA1=1, if no adjacent cells match the criteria
> > AA1
> >> > =0. The names can be in either column.
> >> >
> >> > For instance...
> >> >
> >> > A B
> >> > Phil Tony would give AA1 value 1
> >> > Tony Phil would also give AA1 value 1
> >> > xxx yyy would give AA1 value 0
> >> >
> >> > Thanks
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>