IF - AND - OR statement?
in [Functions]
|
From: duketter on 25 Feb 2010 12:05 Using Excel 2007. I have four cells that either contain a "Y" or are blank. I am trying to analyze if any two of the four cells in A1:A4 have a "Y" then in cell A6 there should be a Y. If only one of the four cells has a "Y" then cell A6 should be blank. I have tried this formula but it only takes into account the first cell in A1: =IF(AND(A1="Y",OR(A2="Y", A3="Y",A4="Y")),"Y","N") I need it to look at all the cells and if any two have a "Y" then a "Y" is entered in A6.
From: NBVC on 25 Feb 2010 12:14 Try: =IF(COUNTIF(A1:A4,"Y")>=2,"Y","") -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 74 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=182642 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
From: Joe User on 25 Feb 2010 12:16 "duketter" wrote: > if any two of the four cells in A1:A4 have a > "Y" then in cell A6 there should be a Y. If > only one of the four cells has a "Y" then > cell A6 should be blank. In A6: =if(countif(A1:A4,"Y")=2, "Y", "") Change "=2" to ">=2" if you meant to say if __at_least__ 2 cells have "Y". ----- original message ----- "duketter" wrote: > Using Excel 2007. I have four cells that either contain a "Y" or are blank. > I am trying to analyze if any two of the four cells in A1:A4 have a "Y" then > in cell A6 there should be a Y. If only one of the four cells has a "Y" then > cell A6 should be blank. > > I have tried this formula but it only takes into account the first cell in > A1: =IF(AND(A1="Y",OR(A2="Y", A3="Y",A4="Y")),"Y","N") I need it to look at > all the cells and if any two have a "Y" then a "Y" is entered in A6. >
From: Glenn on 25 Feb 2010 12:17 duketter wrote: > Using Excel 2007. I have four cells that either contain a "Y" or are blank. > I am trying to analyze if any two of the four cells in A1:A4 have a "Y" then > in cell A6 there should be a Y. If only one of the four cells has a "Y" then > cell A6 should be blank. > > I have tried this formula but it only takes into account the first cell in > A1: =IF(AND(A1="Y",OR(A2="Y", A3="Y",A4="Y")),"Y","N") I need it to look at > all the cells and if any two have a "Y" then a "Y" is entered in A6. > =IF(COUNTIF(A1:A4,"Y")=2,"Y","N")
From: duketter on 25 Feb 2010 14:15 Thanks everyone! This worked perfect. "duketter" wrote: > Using Excel 2007. I have four cells that either contain a "Y" or are blank. > I am trying to analyze if any two of the four cells in A1:A4 have a "Y" then > in cell A6 there should be a Y. If only one of the four cells has a "Y" then > cell A6 should be blank. > > I have tried this formula but it only takes into account the first cell in > A1: =IF(AND(A1="Y",OR(A2="Y", A3="Y",A4="Y")),"Y","N") I need it to look at > all the cells and if any two have a "Y" then a "Y" is entered in A6. >
|
Pages: 1 Prev: Formula results display only after pressing Enter key Next: Saving Pivot Tables |