How to Use IF Statement Based on Cell Color in Excel

Download the Practice Workbook

3 Uses of IF Statement Based on Cell Color in Excel

Example 1 – Calculate the Net Price Based on Cell Color

The sample dataset contains some Products and their Quantity. The rows contain multiple colors, and we will calculate the Net Price of some specific products using the Color Code.

Steps:

• From the Formulas tab, select Define Name.

• A box will appear.
• Write a name (in this case I wrote CellColor) in the Name: section of the box.
• Use the following formula in Refers to and press OK.

• Select cell D5 and insert the following formula:

• Use the AutoFill tool for the entire column.

• Select cell E5 and insert the formula below.

• Use the AutoFill tool for the whole column.

Read More: How to Use Conditional Formatting If Statement Is Another Cell

Similar Readings

• How to Use Multiple IF Statements in Excel Data Validation
• Use IF Statement with Yes or No in Excel (3 Examples)
• Dynamic Data Validation List in Excel with IF Statement Condition
• How to Use IF Statement with Not Equal To Operator in Excel

Example 2 – Find the Discounted Price Based on Cell Color

The dataset contains two columns, Item and Price. The discount is set to 10% and we will find out the Discounted Price of some specific items.

Steps:

• Define another name with the formula below.

• Apply the following formula in cell D5.

• Use the AutoFill tool to apply the formula to the whole column.

• Select cell E5 and insert this formula.

• Apply the AutoFill tool to the entire column in order to get the discounted price.

Read More: How to Find Sum If Cell Color Is Green in Excel (4 Easy Methods)

Example 3 – Compute Bonus on Sales

We have a dataset containing the Names of salespeople and their Sales. The names which have a specific cell color will get a 12% bonus on their sales. Otherwise, the bonus will be zero.

Steps:

• Define another name for ColorCode following a process similar to the one in Example 1.
• Apply the following formula in cell D5 to get the Color Code.

• Use the AutoFill tool for the entire column.

• Select cell E5 and insert the following formula.

• Use the AutoFill tool for the whole column in order to find the bonus for the selected salesmen.

Read More: How to Check If Cell Contains One of Several Values in Excel

Related Articles

• Excel IF Statement Between Two Numbers (4 Ideal Examples)
• How to Prepare IF Statement Contains Multiple Words in Excel
• Use Wildcard with If Statement in Excel (5 Methods)
• Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
• How to Use IF Function with OR and AND Statement in Excel