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. Download the Example FileĪlso read: How to Count Filtered Rows in Excel? #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.
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 Using a Custom Function created using VBA.In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA): #3 Count Colored Using VBA (by Creating a Custom Function).Count Colored Cells using the Color Code.#2 Count Colored Cells Using GET.CELL Function.#1 Count Colored Cells Using Filter and SUBTOTAL.
0 kommentar(er)
