Formula to search for a value in a range of cells

Question

I have a spreadsheet with several cells in one column. Each cell has identical conditional formatting:

• In 1 cell value equal to ='Y'
• In 2 cell value equal to ='N'
• In 3 cell value equal to ='?'

What I would like to achieve is, if all 7 cells contain "Y" as the option then a separate cell will display "Yes", but if any of the cells contain "N" as the selection then the separate cell will display "NO".

How can I achieve this?

Answer 1

One more: use the MATCH function: MATCH("N",a1:a7) If the "N" is found on the range a1:a7, it will return a number, otherwise, #N/D

Then use ISNUMBER and IF: =IF(ISNUMBER(MATCH("N",a1:a7)));"NO";"YES")

PS: for M$-excel use ';' and in OOffice use ','

Answer 2

Try using COUNTIF, e.g. if cells are A2:A8 then

=IF(COUNTIF(A2:A8,"Y")=7,"Yes","No")