Results 1 to 9 of 9

Thread: Multiple IF formulas with text added to cell if all IF formulas come back false

  1. #1

    Multiple IF formulas with text added to cell if all IF formulas come back false



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Okay so I have this in the cell:

    =IF(COUNTIF(B2:AE2,0)=30,A1,)&IF(COUNTIF(B7:AE7,0)=30,A6,)&IF(COUNTIF(B12:AE12,0)=30,A11,)&IF(COUNTIF(B17:AE17,0)=30,A16,)&IF(COUNTIF(B22:AE22,0)=30,A21,)&IF(COUNTIF(B27:AE27,0)=30,A26,)&IF(COUNTIF(B32:AE32,0)=30,A31,)&IF(COUNTIF(B37:AE37,0)=30,A36,)&IF(COUNTIF(B42:AE42,0)=30,A41,)&IF(COUNTIF(B47:AE47,0)=30,A46,)&IF(COUNTIF(B52:AE52,0)=30,A51,)&IF(COUNTIF(B57:AE57,0)=30,A56,)&IF(COUNTIF(B62:AE62,0)=30,A61,)&IF(COUNTIF(B67:AE67,0)=30,A66,)&IF(COUNTIF(B72:AE72,0)=30,A71,)&IF(COUNTIF(B77:AE77,0)=30,A76,)&IF(COUNTIF(B82:AE82,0)=30,A81,)&IF(COUNTIF(B87:AE87,0)=30,A86,)&IF(COUNTIF(B92:AE92,0)=30,A91,)&IF(COUNTIF(B97:AE97,0)=28,A96,)&IF(COUNTIF(B102:AE102,0)=30,A101,)&IF(COUNTIF(B107:AE107,0)=30,A106,)&IF(COUNTIF(B112:AE112,0)=30,A111,)

    And I would Like to place text into the cell if all of these formulas come back false. I have tried everything I can think of but none of my tries have worked.

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi,

    I think you should learn how to use OR and AND functions.
    some examples here : http://www.excel-easy.com/functions/...functions.html

    Good luck

  3. #3
    I have tried to add OR formula to it which I thought was the best option but everything I tried with OR didn't work. I can't figure out the right way to use the OR formula to get it to work.

  4. #4
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi,

    Try with this formula:
    =IF(COUNTIF(B2:AE2,0)=30,A1,IF(COUNTIF(B7:AE7,0)=30,A6,IF(COUNTIF(B12:AE12,0)=30,A11,IF(COUNTIF(B17:AE17,0)=30,A16,IF(COUNTIF(B22:AE22,0)=30,A21,IF(COUNTIF(B27:AE27,0)=30,A26,IF(COUNTIF(B32:AE32,0)=30,A31,IF(COUNTIF(B37:AE37,0)=30,A36,IF(COUNTIF(B42:AE42,0)=30,A41,IF(COUNTIF(B47:AE47,0)=30,A46,IF(COUNTIF(B52:AE52,0)=30,A51,IF(COUNTIF(B57:AE57,0)=30,A56,IF(COUNTIF(B62:AE62,0)=30,A61,IF(COUNTIF(B67:AE67,0)=30,A66,IF(COUNTIF(B72:AE72,0)=30,A71,IF(COUNTIF(B77:AE77,0)=30,A76,IF(COUNTIF(B82:AE82,0)=30,A81,IF(COUNTIF(B87:AE87,0)=30,A86,IF(COUNTIF(B92:AE92,0)=30,A91,IF(COUNTIF(B97:AE97,0)=28,A96,IF(COUNTIF(B102:AE102,0)=30,A101,IF(COUNTIF(B107:AE107,0)=30,A106,IF(COUNTIF(B112:AE112,0)=30,A111,"OracleDeegan")))))))))))))))))))))))

    Good luck
    Last edited by hossat; 2015-06-01 at 11:17 PM.

  5. #5
    Thanks but I already had that formula. Unfortunately I need the cell to be able to show A1,A6,A11,A16,A21,A26,A31,A36,A41,A46,A51,A56,A61,A66,A71,A76,A81,A86,A91,A96,A101, and A106 if all are true or show me only thes that are true. But if none are true then I would like some text to pop up. The formula you gave me will only show the first one that comes up true and not any of the others.

  6. #6
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Could you please post a small sample sheet showing what you have and expected results?

  7. #7
    Okay so currently in D125 the names of anyone who manage to get 0 every time in the over/under row appear. If noone is able to get 0 everytime it remains blank. Instead of being blank I want it the text "they didn't get it" to show up.
    Attached Files Attached Files

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    One way...

    =SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(B2:AE2,0)=30,A1,".")&IF(COUNTIF(B7:AE7,0)=30,A6,".")&IF(COUNTIF(B12:AE12,0)=30,A11,".")&IF(COUNTIF(B17:AE17,0)=30,A16,".")&IF(COUNTIF(B22:AE22,0)=30,A21,".")&IF(COUNTIF(B27:AE27,0)=30,A26,".")&IF(COUNTIF(B32:AE32,0)=30,A31,".")&IF(COUNTIF(B37:AE37,0)=30,A36,".")&IF(COUNTIF(B42:AE42,0)=30,A41,".")&IF(COUNTIF(B47:AE47,0)=30,A46,".")&IF(COUNTIF(B52:AE52,0)=30,A51,".")&IF(COUNTIF(B57:AE57,0)=30,A56,".")&IF(COUNTIF(B62:AE62,0)=30,A61,".")&IF(COUNTIF(B67:AE67,0)=30,A66,".")&IF(COUNTIF(B72:AE72,0)=30,A71,".")&IF(COUNTIF(B77:AE77,0)=30,A76,".")&IF(COUNTIF(B82:AE82,0)=30,A81,".")&IF(COUNTIF(B87:AE87,0)=30,A86,".")&IF(COUNTIF(B92:AE92,0)=30,A91,".")&IF(COUNTIF(B97:AE97,0)=28,A96,".")&IF(COUNTIF(B102:AE102,0)=30,A101,".")&IF(COUNTIF(B107:AE107,0)=30,A106,".")&IF(COUNTIF(B112:AE112,0)=30,A111,"."),REPT(".",23),"They didn't get it"),".","")

    where the 23 in REPT(".",23) is number of cashiers in your formula.

    Note: Your formula didn't include cashier No. 24... not sure if you want to include that and update the REPT() function.


  9. #9
    Thanks that did the trick.

Posting Permissions

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