We help IT Professionals succeed at work.

Excel show "Ready" if multiple cells have value "Good"

Hi experts,

What is the formula in Excel to show "Ready" if multiple cells, see screenshot, have value "Good" ?
I tried formula
=IF(AND(EXACT(Export___Daily_Sheet_Scaling_2_Screening[@[Boomcode check_DSLR_S]:
[Species check_DSI_F]],"Good")),"Ready","Not Ready")

Open in new window

but it gives error "Value"
Excel formula error "Value"
If I use formula
=IF(EXACT([@[Boomcode check_DSLR_S]],"Good"),"Ready","Not Ready")

Open in new window

it works, but that only checks 1 cell.
Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
The AND() needs to contain a list of multiple individual cell comparisons, not a range.

If you have a lot of cells n the range then it might be easier to do a COUNTIF() with a "<>" to the value "Good" and if the count is greater than 0 then assign "Not Ready" else "Ready".


»bp

Commented:
This may help..

https://www.extendoffice.com/documents/excel/2537-excel-compare-if-multiple-cells-are-equal.html

Shows that the exact function can be done over a range
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Ah yes, I forget that AND() works in an array mode too.

So it seems all that is missing is after you enter that formula to press CTRL-SHIFT-ENTER rather than just ENTER.  It should show in the formula bar with curly braces around it, and should do what you wanted.

Of course if any of the cells in the range being checked have errors in them then that will still bubble up to the formula result, but in your screen shot it doesn't look like that is the case...


»bp
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Ahahaha, oh my goodness, so simple. I only needed to press CTRL-SHIFT-ENTER rather than just ENTER.
It works! Thanks experts