Using a RANGE with an IF statement

[RickHasz]

Hi, Excel Experts. My name is Rick and I am a project manager at a telecommunications firm in the United States of America.

I have a problem that I have been trying to solve for several months, but I am no closer to a solution than I was in October 2013. I have a spreadsheet in which I need to identify the status of an order based on individual elements of the order. However, the number of individual elements associated with an order can range from 1 to 150.

I have attached a sample of the spreadsheet with an explanation of what I need the formula to do. Following are some points that you may wonder about as you look at my file:

• Orders (Column A) are unique entities
• Individual Elements of an Order are unique entities
• The Status column (Column C) is where the formula result will be displayed, but only in those rows that also have an entry in the Order column (Column A)
• I have made the entries in the Status column only to demonstrate what the result should be if calculated by a formula
• I already have working formulas for the Count and Range0 columns but I deleted them from the Sample spreadsheet to minimize complexity.
• The criteria for the results in the Status column are:
  • If any entry in Range0 = "Quote", then enter "Pending" in Status
  • Else If any entry in Range0 = "Order", then enter "In Flight" in Status
  • Then enter "Complete" in Status since all entries in Range0 = "Active"

I have tried the following formula but to no avail:
IF(INDIRECT(E2)="Active","Complete",IF(COUNTIF(INDIRECT(E2),"Quote")=D2,"Pending","In Flight")))

I entered the preceding formula as both an array formula and a regular formula. I must I have been beaten by this problem and I would really appreciate any help.

Regards,
Rick

 

[Faseeh]

Hi RickHasz,

Try this with Ctrl+Shift+Enter:

=IF(SUM(((INDIRECT(E2)="QUOTE")*1))>0,"Pending",IF(SUM(((INDIRECT(E2)="Order"))*1)>0,"In Flight","Complete"))

...and see this workbook.

 

[RickHasz]

Hi RickHasz,

Try this with Ctrl+Shift+Enter:

=IF(SUM(((INDIRECT(E2)="QUOTE")*1))>0,"Pending",IF(SUM(((INDIRECT(E2)="Order"))*1)>0,"In Flight","Complete"))

...and see this workbook.

Hi Faseeh,
Thank you, thank you, thank you. This worked perfectly.

Thanks,
Rick

 

[oldchippy]

Rick,

Just to add to Faseeh great formula, you could enclose it in a IFERROR(), again with Ctrl+Shift+Enter and drag it down the column

 

[RickHasz]

Thanks, Oldchippy. Excellent suggestion (especially since I have 2300 rows).

Regards,
Rick

 

[Somendra Misra]

Hi RickHasz,

I was wondering to create the Range0 column in the formula itself, that will reduce the helper column and came with below formula in F2 and copy down (Note this is an array formula so must be entered with Ctrl+Shift+Enter). Just see if this is of any use to you.

=IFERROR(IF(SUM(--(INDEX(INDIRECT(IF(A2="","","B"&ROW(A2)&":B"&IFERROR(ROW(A2)+MATCH(1,--(LEN(A3:A$1000)>0),0),COUNTA(B:B) +1)-1)),)=$G$4))>0,"PENDING",IF(SUM((--(INDEX(INDIRECT(IF(A2="","","B"&ROW(A2)&":B"&IFERROR(ROW(A2)+MATCH(1,--(LEN(A3:A$1000)>0),0),COUNTA(B:B) +1)-1)),)=$H$4)))>0,"In Flight","Complete")),"")

Please have a look of attached file also.

Regards,