In the worksheet shown below, CellColor is used in cell D5 like this: =CellColor Once you have defined the named range, you can use it in a column directly to the right of the cell you want color information for. To do this, place the cursor in cell B1, then create a new named range called "CellColor" that refers to cell A1 like this: =GET.CELL(38,A1)īecause we are using relative references, this essentially means: get the color code of the cell directly to the left. GET.CELL can't be used in a formula directly, you must first create a named range that uses GET.CELL to get the color of the cell directly to the left. not with conditional formatting), you can use an obscure function called GET.CELL to retrieve cell color as a numeric code, then use this code to calculate the subtotals you need, as seen in the worksheet below:
In cases where cell color has been applied manually (i.e. For that situation, see the GET.CELL option below. It won't handle cases where color is applied to cells randomly, or in a way that can't be mimicked in a formula.
This approach only works in cases where cell color is being applied with rules that can be applied in a formula. The formulas in G5:G6, and H5:H6 use this same criteria to count and sum the data. The formula in cell G5 is: =COUNTIF(amount,F5) When a value is greater than 1200, green formatting is applied. When a value in column C is less than 1000, yellow formatting is applied. In the screen below, the green and yellow color is applied with two separate conditional formatting rules. However, it is a simple and clean way to solve the problem in compatible scenarios. This is an indirect way to solve this problem because the formula is not actually checking cell color but is instead applying logic that mirrors the rules that were used to apply the color. If color is being applied based on specific rules (either with conditional formatting or manually) you may be able to use standard logic that follows the same rules to count and sum by color. There are several different approaches, as explained below. This is a tricky problem, because there is no Excel function that will let you count cells by color directly. In this example, the goal is to subtotal (count and sum) values based on cell color.
0 kommentar(er)
