How Excel uses the Get.cell function to complete cell color summation

When we use Excel, we encounter situations where some cells in a worksheet are filled with a color, requiring a quick sum of cells filled with a color.

Excel's ready-made function is a function that does not sum by color.

Small series in this article to introduce a method: the use of macro function Get.cell to complete according to the color of the cell.

Examples are shown in the following illustration:

The A1:G10 range of cells in the worksheet is the source data, and in the area of a1:g10, some cells fill in red, and how to quickly find the and the cells with a red cell fill color.

The following is an introduction to the cell fill color:

The first step is to position the cursor in cell A12, click the menu insert-name-definition, pop the Definition Name dialog box, enter "HHH" under "name in the current workbook", and under "Reference position", enter: =get. CELL (63,sheet1! A1:G10), click OK to close the Define Name dialog box.

In the second step, select the A12:g21 cell range, enter in the formula bar: =hhh, press CTRL + ENTER to determine. To get the color code of the colored cells, the red color is 3.

The third step, in the D23 cell input formula: =SUMIF (A12:G21,3,A1:G10), to get A1:G10 cell range color Red data is: 103.

Case resolution:

1, in the definition name, the reference location uses the Get.cell macro function.

2, =get. CELL (63,sheet1! A1:G10), the parameter "63" in the formula means: Cell fill color (background) encoded digits.

3, =SUMIF (A12:G21,3,A1:G10), the formula of the parameter "3", is the corresponding red encoding 3.