If Column X contains specific text in Column Y then SUM Column Z

[Scerabi]

I'm working with a large table similar to the one below with thousands of Order #'s (Column A). In Column D I'm trying to identify the status of each Order # based upon the Line Description(s) in Column B associated with each unique Order #. If Column B contains "Fiber Facility - Enterprise" then I know the Order is "Pending Facility" and want to identify it as "Pending Facility" in Column D. What's the best way to do this? Thank you! Greatly appreciate the assistance.

If ORD-001938 in Column A, contains "Fiber Facility - Enterprise" in Column B, then enter the text "Pending Facility" in Column D.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Order #[/TD]
[TD]Line Description[/TD]
[TD]New MRC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]ORD-001938[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dedicated Internet Access (DIA)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]Pending Facility [/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]ORD-001938[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]Fiber Facility - Enterprise[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]ORD-001938[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]50 Mbps DIA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

1. Lacking in example data, so this may not work, more test data = more accurate solution.

2. Your subject says SUM Column Z.
There is NO column Z in your example data.

To set column D to "Pending Facility" when column B is "Fiber Facility - Enterprise" use

in D2
=IF(LEFT(B2,27)="Fiber Facility - Enterprise","Pending Facility","")
and copy down the column.

 

[Scerabi]

Thank you for the info and constructive criticism. Will post a new thread with better explanation and example.