Forum Discussion
James818
Aug 15, 2023Brass Contributor
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...
mtarler
Aug 15, 2023Silver Contributor
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")
James818
Aug 15, 2023Brass Contributor
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!
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!