{"id":3887,"date":"2023-02-03T20:08:22","date_gmt":"2023-02-03T20:08:22","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=3887"},"modified":"2024-03-13T19:07:22","modified_gmt":"2024-03-13T19:07:22","slug":"how-to-count-colored-cells-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-count-colored-cells-in-excel\/","title":{"rendered":"How to Count Colored Cells in Excel?"},"content":{"rendered":"

Count Colored Cells in Excel to unlock a new dimension of data analysis and reporting. This technique enhances your ability to quickly interpret and act upon data categorized by color, making it indispensable for project management, budgeting, and inventory control. By mastering the skill to count colored cells, you can streamline workflows, improve accuracy, and present data in a more intuitive and accessible manner. Elevate your Excel skills by incorporating this function into your data management strategy, and transform color-coded information into actionable insights.<\/p>\n

This Content Covers:<\/strong><\/p>\n

    \n
  1. How to Count Colored Cells in Excel<\/strong><\/a>?<\/strong><\/li>\n<\/ol>\n

    1.1 Using the Find and Select option<\/strong><\/p>\n

    1.2 <\/strong>Using Filter and SUBTOTAL<\/strong><\/p>\n

    1.3 <\/strong>Using GET.CELL Function<\/strong><\/p>\n

    1.4 <\/strong>Using VBA <\/strong><\/p>\n

    1. How to Count Colored Cells in Excel<\/strong>?<\/strong><\/h2>\n

    1.1 Using the Find and Select option<\/strong><\/h3>\n

    Suppose you have a table like this and you want to extract the information about how many students have passed or failed based on the Result column\u2019s-colored cells. Here color green represents Pass and the color red represents Fail. Follow the steps below to learn how you can count colored cells using Excel\u2019s Find and Select option.<\/p>\n

    \"Count<\/p>\n

    Step 1: <\/strong>Go to Home Tab>>Find & Search>>Find. <\/strong>Or press CTRL+F<\/strong> shortcut key to open Find and Replace dialogue box.<\/p>\n

    \"Count<\/p>\n

    Step 2: <\/strong>From Find and Replace<\/strong> dialogue box, click on the drop-down button of Format <\/strong>and select Choose Format From Cell.<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 3: <\/strong>You will see a dropper like icon on your worksheet, now take that on any of the colored cell which you want to count and click on it. The Preview <\/strong>box inside Find and Replace<\/strong> dialogue box will display that color for you. Click on Find All<\/strong> and all the cells that matched the formatting are displayed in the lower portion of the Find and Replace<\/strong> dialog box. The total count of that particular-colored cells can be found in the lower left corner. So now you know that 4 students have passed the test.<\/p>\n

    \"Count<\/p>\n

    1.2 <\/strong>Using Filter and SUBTOTAL<\/strong><\/a><\/h3>\n

    Step 1: <\/strong>Right click on any cell of the table and select Filter>>Filter by Selected Cell\u2019s Color<\/strong>. Or just use CTRL+SHIFT+L<\/strong> shortcut keys to apply filters in your data table.<\/p>\n

    \"Count<\/p>\n

    Step 2: <\/strong>Insert the following formula in a cell where you want the count to be displayed and hit Enter<\/strong> key. This will show you how many cells are there in that range that have a data in them. The number 2 in the formula is the numerical indicator for the COUNT <\/strong>function.<\/p>\n

    =SUBTOTAL(2,range)<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 3: <\/strong>Now click on the filter button>>select Filter by Color <\/strong>and choose the color you want.<\/p>\n

    \"Count<\/p>\n

    Step 4: <\/strong>Here I have selected color red to know how many students have failed in the test and B10 cell showing me the exact amount of red colored cells visible. So now I know that 3 of the students have failed in the test.<\/p>\n

    \"Count<\/p>\n

    1.3 <\/strong>Using GET.CELL Function<\/strong><\/a><\/h3>\n

    Due to compatibility concerns, the Macro4 function GET.CELL has been preserved. If you utilize it as one of the worksheet’s normal functions, it won’t function. It also won\u2019t work with conditional formatting; however, this function works with Excel named ranges.<\/p>\n

    Suppose we have this data table and now we want to count the colored cells using GET.CELL<\/strong> function. Follow the steps below:<\/p>\n

    \"Count<\/p>\n

    Step 1: <\/strong>Create a Helper column in column D and select cell D2.<\/p>\n

    \"Count<\/p>\n

    Step 2: <\/strong>Go to Formulas>>Name Manager<\/strong> and from the Name Manager<\/strong> dialogue box select New.<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 3: <\/strong>Inside the New Name<\/strong> dialogue box, name your formula something that you can remember in the Name <\/strong>box. For Scope<\/strong> select Workbook.<\/strong> Now insert this GET.CELL<\/strong> formula given below inside Refers to<\/strong> box. Here the number 38 inside the formula is a parameter that returns the shade of the foreground color of the selected cell. Now click OK>>Close.<\/strong><\/p>\n

    =GET.CELL(38,SheetName!colored cell)<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 4: <\/strong>Select cell D2 and start typing the formula you just created and you will see the formula in the drop-down menu. Click on it and hit Enter. This will convert the color from cell C2 into a numerical form in D2. Now drag D2 down to D8 to apply the formula in the whole column of this table. You have the numerical value for each of the colors available in your data sheet which are different from each other.<\/p>\n

    \"Count<\/p>\n

    Step 5: <\/strong>Now select cell G2 and insert the following formula in that cell and hit Enter key. The digit 36 inside the formula is the numerical value of color Green. When you hit the Enter key this cell will display the count for color Green in the data table.<\/p>\n

    =COUNTIF(range of numerical value,[numerical value for a particular color])<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 6: <\/strong>Repeat this formula in cell G3, G4 and G5 with the color\u2019s numerical value from the helper column to have the count of these colored cells.<\/p>\n

    \"Count<\/p>\n

    1.4 <\/strong>Using VBA <\/strong><\/a><\/h3>\n

    Step 1: <\/strong>Right click on the worksheet name and select View Code.<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 2:<\/strong> Insert the below given code inside VBA Code<\/strong> box and then close the VBA window.<\/p>\n

    Function COLORCOUNT(CountRange As Range, FillCell As Range)<\/strong><\/p>\n

    Dim FillColor As Integer<\/strong><\/p>\n

    Dim Count As Integer<\/strong><\/p>\n

    FillColor = FillCell.Interior.ColorIndex<\/strong><\/p>\n

    For Each c In CountRange<\/strong><\/p>\n

    \u00a0\u00a0\u00a0 If c.Interior.ColorIndex = FillColor Then<\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Count = Count + 1<\/strong><\/p>\n

    \u00a0\u00a0\u00a0 End If<\/strong><\/p>\n

    Next c<\/strong><\/p>\n

    COLORCOUNT = Count<\/strong><\/p>\n

    End Function<\/strong><\/p>\n

    \"Count<\/p>\n

    Step 3: <\/strong>Select cell E2 and start typing =vba<\/strong> and you will see the VBAProject<\/strong> formula. Select it then select the range and the color which you want the count of. Click Enter and the cell will display how many cells are there of that particular color.<\/p>\n

    \"Count<\/p>\n

    Step 4: <\/strong>Follow the previous step for the other colors too.<\/p>\n

    \"Count<\/p>\n

    Application of Count Colored Cells in Excel<\/strong><\/h2>\n