This formula would return 0 if there is NO background color in a cell and would return a specific number if there is a background color. In the cell adjacent to the data, use the formula =GetColor You need to use the reference of the column where you have the cells with the background color. In the above formula, I have used Sheet1!$A2 as the second argument. Let’s deep dive and see what to do in each of the three mentioned steps. Using the Color Number to Count the number of Colored Cells (by color).Use the Named Range to get color code in a column.Create a Named Range using GET.CELL function.Here are the three steps to use GET.CELL to count colored cells in Excel: See Also : Know more about GET.CELL function. It does not work if used as regular functions in the worksheet. GET.CELL is a Macro4 function that has been kept due to compatibility reasons. #2 Count Colored Cells Using GET.CELL Function If the data if not filtered it returns 19, but if it is filtered, then it only returns the count of the visible cells. The SUBTOTAL function uses 102 as the first argument, which is used to count visible cells (hidden rows are not counted) in the specified range. In the above dataset, since there are two colors used for highlighting the cells, the filter shows two colors to filter these cells.Īs soon as you filter the cells, you will notice that the value in the SUBTOTAL function changes and returns only the number of cells that are visible after filtering. Go to ‘Filter by Color’ and select the color.This will apply a filter to all the headers. Go to Data –> Sort and Filter –> Filter.In any cell below the data set, use the following formula: =SUBTOTAL(102,E1:E20).Here are the steps count colored cells in Excel: There are two background colors used in this data set (green and orange). Suppose you have a dataset as shown below: Use the SUBTOTAL function to count colored cells that are visible (after filtering). To count colored cells in Excel, you need to use the following two steps: #1 Count Colored Cells Using Filter and SUBTOTAL
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |