Array to meet conditions
 

I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin

Array to meet conditions
 

Try this:

=IF(COUNTIF(B37:Q37,"OK")=COLUMNS(B37:Q37),"OK","E RROR")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Erin" wrote:

I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin

Array to meet conditions
 

Both regular formulas, not array formulas

=IF(COUNTIF(B37:Q37,"OK") = COUNTA(B37:Q37),"OK","ERROR")
or
=IF(COUNTIF(B37:Q37,"OK") = (COLUMN(Q37)-COLUMN(B37)+1),"OK","ERROR")

The first one will not count completely blank cells...the second one will...

HTH,
Bernie
MS Excel MVP


"Erin" wrote in message
...
I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin

Array to meet conditions
 

Perfect, thank you! I had never used the columns function before - that will
go in my notebook!

"Ron Coderre" wrote:

Try this:

=IF(COUNTIF(B37:Q37,"OK")=COLUMNS(B37:Q37),"OK","E RROR")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Erin" wrote:

I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin

Array to meet conditions
 

Thanks for the feedback, Erin....I'm glad I could help.

***********
Regards,
Ron

XL2002, WinXP


"Erin" wrote:

Perfect, thank you! I had never used the columns function before - that will
go in my notebook!

"Ron Coderre" wrote:

Try this:

=IF(COUNTIF(B37:Q37,"OK")=COLUMNS(B37:Q37),"OK","E RROR")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Erin" wrote:

I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin

Array to meet conditions
 

=IF(AND(B37:Q37="OK"),"OK","ERROR")

ctrl+shift+enter, not just enter


"Erin" wrote:

I have a row of formula results that will either have a value of "OK" or the
result of a vlookup. In the next column, I am trying to write a formula that
will say "OK" if ALL of the other columns also say OK so I can sort out the
rows that are not OK. (Imagine columns B-Q have the OK or vlookup results
and column R is trying to summarize those results). So in column R I have
tried:

{=IF((B37:Q37)="OK","OK","ERROR")}

The problem here is that I get a result of OK if ANY of cells in the array
say OK. I only want a result of OK if ALL the cells in the array say OK.

What am I missing? (Or is there a completely different better way to do
this?)

Thank you!

Erin