Forum Discussion

James818's avatar
James818
Brass Contributor
Aug 15, 2023

If one of multiple cells contain specific text then return value

I am stumped on work project.

 

I have a cell F13 intended to be dependent on a range of 4 other cells.

 

When all cells are No, I just need F13 to stay blank. So far this works.

 

Range F5, F7, F9 & F11 will either be "No" or Not Effective". If any of those cells just has 1 Not effective, I want to make F13 to populate as "Not Effective".

 

If I can only select those cells specifically that would be perfect as in between is just rows as a spacer.

 

 

 

Here are my formulas.

 

F13, this cell currently works only if all cells are Not effective.

=IF(AND(F5="Not Effective",F11="Not Effective"),"Not Effective","No")

 

I attached a link to my file on google drive.

 

Thank you in advance.

 

https://docs.google.com/spreadsheets/d/1tgQbOQjgpP33nw91Ahl-lz04cezLVMwZ/edit?usp=sharing&ouid=114356740515231394022&rtpof=true&sd=true

excel
Formulas and Functions

7 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 15, 2023

    James818 

    Just for entertainment, I returned each of the 4x6 array of assessment criteria with a single dynamic array formula.  Then I aggregated the table to give the summary row with a second formula.

     

    Worksheet formula to give 2D array
= IF(keyDates, IF((keyDates>=startDates) * (keyDates<=endDates),text,"No"), "")

Worksheet formula to give summary row
= IF(BYCOL(status=text, ORλ), text, "No")

Lambda function
ORλ
= LAMBDA(x, OR(x))

     

    The key thing I would note is that current Excel formulas bear little to no resemblance to traditional spreadsheet methods.

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 15, 2023

    James818 

    I think an OR condition would capture the cases you need while ignoring the FALSE arising from the intervening blank cells.

    = IF( OR(statusColF = "Not effective"), "Not effective", "")

    If you needed to remove the blanks, that is straightforward with 365 and the array shaping functions WRAPROWS and TAKE.

    • James818's avatar
      James818
      Brass Contributor
      Aug 15, 2023

      PeterBartholomew1 

       

      Thank you 

       

       

      With everything my boss keeps adding and subtracting form our chart I keep having to change formulas.   Thank you. 

       

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 15, 2023

        James818 

        Some things are easier with array formula and Lambdas; others you just have to bite the bullet and get in there with a knife and fork.  The times Lambda wins out are when a single change in the Lambda propagates throughout the workbook.  If every formula is a one off then it gains you little and can even increase your problems.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 15, 2023
    By "If any of those cells just has 1 Not effective" means at least 1 of those cells is 'not effective'
    then it would be easy to use this but it does include the rows in between
    =IF(COUNTIF(F5:F11,"Not Effective")>=1, "Not Effective", "No")
    alternatively you can change that to be =1 instead
    if you don't want to include those inbetween rows you could try
    =IF(SUM(--(INDEX(F5:F11,{1,3,5,7})="Not Effective"))>=1,"Not Effective", "No")
    • James818's avatar
      James818
      Brass Contributor
      Aug 15, 2023
      mtarler Thank you.
      The first one works perfect, =IF(COUNTIF(F5:F11,"Not Effective")>=1, "Not Effective", "No")

      I tested the 2nd formula, and it did not change. Fortunate the first one worked I hoped for.

      Luckily when this does turn into a final chart, I will lock cells only for what can be filled in only, so the in between rows should not be an issue once the sheet is protected.

      Thank you so much!

Resources