Array If formula

  • Thread starter Thread starter steven.britton
  • Start date Start date
S

steven.britton

I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.
 
Assuming you mean that *every* cell in the range must contain "Yes".

Array entered** :

=IF(AND($AC$136:$AC$146="Yes"),"Yes", "No")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Interesting. You could do it the brute-force way
(=IF(AND($AC$136="Yes",$AC$137="Yes"...),"Yes","No"), but that would be
ridiculous if there were more than a handful of them. Let's see...

Oh, of course. Try COUNTIF, to count how many "Yes" values there are in
$AC$136:$AC$146, and if it's 11 say "Yes", otherwise "No". Like this:

=IF(COUNTIF($AC$136:$AC$146,"Yes")<11,"No","Yes")

If the size of the range might vary, get the formula to use the ROWS
function to count how many rows there should be:

=IF(COUNTIF($AC$136:$AC$146,"Yes")<ROWS($AC$136:$AC$146),"No","Yes")

Or name the range and then use the name:

=IF(COUNTIF(Flags,"Yes")<ROWS(Flags),"No","Yes")
 
Hello,

Or
=IF(SUMPRODUCT(--(AC136:AC146<>"Yes")),"No","Yes")
entered normally.

Regards,
Bernd
 
Hi,

Let's just take your idea and modify it a tad:

=IF(AND(AC136:AC146="Yes"),"Yes","No")

=IF(OR(AC136:AC146="No"),"No","Yes")
(if by flip you mean the cells contain either Yes or No.)

=IF(OR(AC136:AC146<>"Yes"),"No","Yes")
(This works like the first one regardless)

all are array entered.
 
Hi,

And one other one

=IF(COUNTIF(AC136:AC146,"<>yes"),"No","Yes")

and if the cell are either empty or Yes then
=IF(COUNTIF(AC136:AC146,""),"No","Yes")

both are not array entered.
 
Back
Top