Excel Formula Based On Cell Color

Use Excel formula to change background color of special cells On the Home tab in the Styles group click Conditional Formatting New Rule see step 2 of How to dynamically change. Now to achieve the sum of cells that are colored in excel enter the formula for SUBTOTAL below the data table. You can overwrite the formula written earlier in cell E4 and replace it with the following code and then push return. The formula that is entered to calculate the summation is SUBTOTAL9D2D13 Here number 9 in the function_num argument refers to sum functionality and the reference argument is given as the range of cells to be computed.

How To Count Colored Cells In Excel Step By Step Guide Video

Method 1 Count Cells With Color Using Filter Method with Sub Total Function Method 2 Count Cells with Color By Creating Function using VBA Code Now let us discuss each of the methods in detail along with an example.

Excel formula based on cell color. Assigning a number for background color of C3 using the named range Color. Jump down to the Refers to part and enter the following. To count cells by color please enter the formula COUNTIFF2F20NumColor.

This number is specific to a color so all the cells with the same background color get the same number. GETCELL38Sheet1A2 This formula will retrieve the colour index number for the background colour of cell A2. Select cell D3 and enter the formula.

Instead of a formula based on the color of a cell it is better to write a function that can detect the color of the cell and manipulate the data accordingly. The syntax for the SUBTOTAL formula is shown below. Now select Use a formula to determine which cells to format option and in the box type the formula.

We can then write the following IF statement to apply a discount to products displayed with a red background. Next press ALT F11 to open the VB Editor. In both formulas F2F20 is the Color column NumColor is the specified named range E2E20 is the Amount Column and you can change them as you need.

Sum Cells Based On Background Color