Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use of Offset function in array formula scabHead Excel Worksheet Functions 4 December 23rd 06 02:16 AM
Find a range of values in a range of cells Jack Taylor Excel Worksheet Functions 20 November 25th 06 02:26 PM
Array formula with 2 conditions... JR573PUTT Excel Discussion (Misc queries) 0 February 16th 06 09:44 PM
Excel array formulas Les Gordon Excel Discussion (Misc queries) 1 September 3rd 05 04:12 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 07:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"