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.
=GET.CELL(38,'Example 1'!B5)
- Select cell D5 and insert the following formula:
=CellColor
- Use the AutoFill tool for the entire column.
- Select cell E5 and insert the formula below.
=IF(CellColor=40,C5*$E$12,"-")
- 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.
=GET.CELL(38,'Example 2'!B5)
- Apply the following formula in cell D5.
=ColorCell
- Use the AutoFill tool to apply the formula to the whole column.
- Select cell E5 and insert this formula.
=IF(ColorCell=39,C5-C5*$E$12,"No Discount")
- 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.
=ColorCode
- Use the AutoFill tool for the entire column.
- Select cell E5 and insert the following formula.
=IF(CellColor=40,C5*$E$12,0)
- 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
what version of excel are you using for this example? I tried to create the formula in Excel 365 and the formula returns 0
Hello Azada Rudnicki,
We use Microsoft Excel 365 to demonstrate the examples. The reason of getting 0 is GET.CELL function is not working properly or not defined in the Name Manager. IF function works with existing values there is no problem in the formula.
Before using the IF formula you must need to create the name manager by using GET.CELL function.
Steps:
=GET.CELL(38,'Example 1'!B5)=CellColorRegards
ExcelDemy