Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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 ','

share|improve this answer
2  
for exact match you should have a zero as third argument in MATCH like =MATCH("N",A1:A7,0) – barry houdini Oct 17 '12 at 17:18

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

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .