Forum Discussion
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...
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")
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")
- 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!
