|
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" wrote in message . 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" wrote in message ... Hi Phil In cell AA1 =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1 -- Regards Roger Govier "Phil" wrote in message . 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={"Phi l","Tony"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Phil" wrote in message . 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" wrote in message ... Hi Phil In cell AA1 =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1 -- Regards Roger Govier "Phil" wrote in message . 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" wrote in message . 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" wrote in message ... Hi Phil In cell AA1 =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1 -- Regards Roger Govier "Phil" wrote in message . 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" wrote in message ... 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={"Phi l","Tony"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Phil" wrote in message . 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" wrote in message ... Hi Phil In cell AA1 =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1 -- Regards Roger Govier "Phil" wrote in message . 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" wrote in message ... 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" wrote in message . 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" wrote in message ... Hi Phil In cell AA1 =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1 -- Regards Roger Govier "Phil" wrote in message . 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 |
| All times are GMT +1. The time now is 06:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com