Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
Array formula with 2 conditions... | Excel Discussion (Misc queries) | |||
Excel array formulas | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |