Array If formula

[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.

 

[T. Valko]

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.

 

[Bob Bridges]

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")

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Shane Devenshire]

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.

 

[Shane Devenshire]

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.