+ Reply to Thread
Results 1 to 7 of 7

More than one "If" statement

  1. #1
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Phoenix, Az
    MS-Off Ver
    MS Office 365
    Posts
    138

    More than one "If" statement

    Hello all.
    I am attempting to make a spreadsheet for my job where certain questions needs to be answered Yes or No. I am using a drop down for the Yes / No.
    I have one cell where if anyone answers one of the questions "Yes" it says "Please Proceed" and if you answer "No", it says "Stop the Test". The problem, I have 7 questions that someone has to answer.
    I know it requires the use of more than one "If" statement so that all the Yes/No answers get looked at. But I can't get the particulars figured out.

    Cells M16 to M22 have the Yes / No answer and cell M24 is the cell I am using for the statements "Please Proceed" and "Stop the Test"
    I have the basic formula in cell M24 of =IF(M16= "Yes", "Please Proceed", "Stop the Test")
    I know I am close..and feel dumb asking, but can't figure it out.

    Thanks all.
    Dave

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,865

    Re: More than one "If" statement

    Do you mean that you want to check all the answers for at least one Yes? If so, you can do this:

    =IF(COUNTIF(M16:M22,"Yes")>=1,"Please Proceed","Stop the Test")

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Phoenix, Az
    MS-Off Ver
    MS Office 365
    Posts
    138

    Re: More than one "If" statement

    Pete - you are close.
    I want to check all the answers for at least one No.

    Dave

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,865

    Re: More than one "If" statement

    Well, try this then:

    =IF(COUNTIF(M16:M22,"No")>=1,"Stop the Test","Please Proceed")

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Phoenix, Az
    MS-Off Ver
    MS Office 365
    Posts
    138

    Re: More than one "If" statement

    Great Pete.
    Thanks. One more question. If my Yes/No cells are blank when the excel sheet opens, is there a way of inserting something into formula so cell M24 blank at least until one of the cells (M16-M22) are chosen Yes/no?

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,865

    Re: More than one "If" statement

    You could do this:

    =if(len(M16&M17&M18&M19&M20&M21&M22)=0,"",IF(COUNTIF(M16:M22,"No")>=1,"Stop the Test","Please Proceed"))

    This will show a blank if all those cells are empty, but if you put a No or Yes in one of them then you will get one of the other messages. To avoid this happening so soon after starting the test, you might like to think about having another cell where you can drop-down and choose "Finished" once all the other cells have been completed, and adjust the formula so it will only give a response once Finished has been selected in that cell.

    Hope this helps.

    Pete

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: More than one "If" statement

    Slightly shorter..

    =IF(COUNTA(M16:M22)=0,"",IF(COUNTIF(M16:M22,"No")>=1,"Stop the Test","Please Proceed"))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1