Hi All,
Hoping you all could help me. I am trying to create a new column (Invoiced Price) in my data table that changes the value from the total cost column based on the invoice # and a keyword in the product description column.
If the product description has the keyword "package" listed in any row for that invoice #, I need the Invoiced Price to display what's listed in the Total cost column for the row that has the keyword "package" and to list zero for everything else listed on that particular invoice #. If the product description does not have the keyword package, I need it to list everything from the total cost column in the Invoiced Price.
In the example below, Invoice # 2025-0001 is a TV Package and everything that's included in that package (tv, tv mount, speakers, and HDMI cables). Since the package was purchased, the Invoiced price should show the TV package total cost of $4500 and everything that's included in that package deal (tv, tv mount, speakers, and HDMI cables) should show as $0. Invoice # 2025-0008 was not a package deal, so I would need the Invoiced Price to show what's listed in the total cost column.
| Invoice # | Invoice Date | Item # | Product Description | Qty | Price | Total Cost | Invoiced Price |
| 2025-0001 | 1/25/25 | PKG-TV | TV Package | 1 | $4,500.00 | $4,500.00 | $4,500.00 |
| 2025-0001 | 1/25/25 | TV-01 | 100" TV | 1 | $3,000.00 | $3,000.00 | $0.00 |
| 2025-0001 | 1/25/25 | MNT-01 | TV Mount | 1 | $50.00 | $50.00 | $0.00 |
| 2025-0001 | 1/25/25 | SPKR-01 | S.S. Speakers | 1 | $1,600.00 | $1,600.00 | $0.00 |
| 2025-0001 | 1/25/25 | HDMICBL-01 | HDMI cable | 4 | $15.00 | $60.00 | $0.00 |
| 2025-0008 | 4/9/25 | TV-05 | 75" TV | 1 | $2,500.00 | $2,500.00 | $2,500.00 |
| 2025-0008 | 4/9/25 | MNT-80 | TV Mount | 1 | $55.00 | $55.00 | $55.00 |
Your help is greatly appreciated!
