Next type the formula =NumColor, and the drag the AutoFill handle to apply the formula to other cells in the Color column. Now add a new Color column right to the original table. (2) Enter the formula =GET.CELL(38,Sheet4!$E2) in the Refers to box ( note: in the formula, 38 means return the cell code, and Sheet4!$E2 is the first cell in the Amount column except the column header which you need to change based on your table data.)ģ. In the New Name dialog, please do as below screenshot shown:
#Excel formula for row color code#
In this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Note: This method requires the colored cells you will count or sum are in the same column. See screenshot:Īfter filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. Click the Filter icon in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. Select the header of the table, and click Data > Filter. Note: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.Ģ.
![excel formula for row color excel formula for row color](https://i0.wp.com/basicexceltutorial.com/wp-content/uploads/2020/03/031720_0705_Excelformul5.png)
In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel.ġ.
![excel formula for row color excel formula for row color](https://benisnous.com/wp-content/uploads/2020/11/How-to-apply-color-in-alternate-rows-or-columns-in.png)
Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column.