Question

Excel: Counting colored cells - this is how it works

Answer 1

Color numbers for colored cells in Excel
1st step:
2nd step:
3rd step:
4th step:
Count colored cells in Excel
1st step:
2nd step:
3rd step:
Quick guide: Color numbering colored cells in Excel
Quick guide: Counting colored cells in Excel

---

Would you like to know how many colored cells are in your Excel document? We'll show you how to count these..

There are many ways in which you can keep track of your spreadsheets in Microsoft Excel. One of them is the colored marking of cells. In order to then still know how many cells of which color there are, you can have Excel count them. Unfortunately, this does not work with a simple formula, but requires a few additional steps. You will find everything important in these instructions.

• Color numbers for colored cells in Excel
• Count colored cells in Excel

Color numbers for colored cells in Excel

Follow our step-by-step instructions or take a look at the brief instructions .

Attention: If a color changes in a cell, this is not automatically adjusted in the color code column. You have to do this by hand.

1st step:

Click on the " Formulas " tab in the menu bar. Select "Define name ". In the new window, give your formula a name, for example " Colors ". Then you have to decide on a formula, depending on whether you use colored cells or colored font within the cells: = CELL .ZUORDNEN (63; INDIREKT ("ZS (-1)";)) with a colored background = ZELLE.ZELLE.ZUORDNEN (24; INDIREKT ("ZS (-1)";)) with colored text Finally click on " OK " .

2nd step:

Go to the top field of an adjacent column and enter = [formula name] . In the example you have to enter = colors . Confirm your entry with [Enter] .

3rd step:

Grab the small square in the lower right corner of the cell and drag the entire section down until it has a number assigned to each cell in the adjacent column.

4th step:

You will now successfully receive an entire column with all the color codes of the adjacent column. It continues in the next section.

Count colored cells in Excel

The next step is to determine the number of cells of each color. Follow our step-by-step instructions or take a look at the brief instructions ..

1st step:

Click in an empty cell and type = COUNTIF (B: B; 35) . The " B: B " refers to the fact that all of the color numbers are in column B here. If the color codes are in column C in your project, you would then have to enter " C: C " instead of " B: B ". " 35 " indicates the color code, i.e. the code of the green cells here. You can find the color code in the column that you created using the instructions above. If you use a different color you will of course have to change the color code.

2nd step:

Repeat the first step for all other color codes as well . For the sake of clarity, the numbers of the counted cells have been colored here by hand. This does not happen automatically and is only used here for illustration purposes.

3rd step:

If you want, you can create an additional cell . Here you just write = [color counting cell 1] + [color counting cell 2] etc., so in the example = C1 + C2 + C3 . This gives you an overview of how many cells are actually colored.

Quick guide: Color numbering colored cells in Excel

1. Open the tab " Formulas " and click on " Define name ".
2. Find a suitable name for the formula, for example " Colors ". Enter this under " Name: ".
3. Now you have to enter one of two formulas for " Refers to: ":
   =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";)) with a colored background
   =ZELLE.ZUORDNEN(24;INDIREKT("ZS(-1)";)) with colored text
4. Then click OK .
5. Now go into a column that is adjacent to your colored column. Enter there =[Formelname] , for example, =Farben and confirm by pressing the [Enter] key.
6. Using the small square at the bottom right of the cell, drag the contents down to the entire column so that each cell in the colored column is also assigned a labeled cell in the adjacent column. The numbers in the column just created reflect the color number in which the cells are colored.

Quick guide: Counting colored cells in Excel

1. Select a cell that will later show the number of colored cells. Write the following formula there: The "B: B" means that the entire column B should be searched for the specified number (i.e. the number of your color code). If you have the evaluation of the color codes in another column, such as column C, you have to enter the corresponding column designation instead, for example "C: C". The 35 refers to the color code, here a light green. The color code is generated automatically by Excel, so all you have to do is read it off. You need to match this part to the color code of the color you are using. =ZÄHLENWENN(B:B,35)

   
2. Repeat this step for all colors used.
3. If you want, you can in a further cell nor =[Farbzähl-Zelle 1]+[Farbzählzelle 2] enter so. That can be for example =C1+C2+C3 . This shows you the entirety of all the colored fields in your table.

---