**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula that counts the number of colored cells in a column, regardless of color.

Solved by S. J. in 32 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
10/08/2018 - 07:59

Hi..

User
10/08/2018 - 08:00

Hello

Excelchat Expert
10/08/2018 - 08:01

Based on the description, i understand you would need the count of colored cells in a column..

User
10/08/2018 - 08:01

that's exactly correct

Excelchat Expert
10/08/2018 - 08:01

give me few mins to build this.. and share the file..

User
10/08/2018 - 08:01

thank you!

Excelchat Expert
10/08/2018 - 08:07

i have built the solution.. but the file is of .xlsm extension...

Excelchat Expert
10/08/2018 - 08:08

so will upload it onto dropbox and provide the link

User
10/08/2018 - 08:08

ok

Excelchat Expert
10/08/2018 - 08:09

https://www.dropbox.com/s/f9juiqmscrzv1an/CountCellColor.xlsm?dl=0

Excelchat Expert
10/08/2018 - 08:09

please download the file from this location...

Excelchat Expert
10/08/2018 - 08:09

let me know if this looks fine..

User
10/08/2018 - 08:13

is this using conditional formatting?

Excelchat Expert
10/08/2018 - 08:13

no this is using a feature built in the excel..

Excelchat Expert
10/08/2018 - 08:14

I'm marking the session as DONE.. I will provide an explanation on how this can be implemented in your file..

Excelchat Expert
10/08/2018 - 08:15

In your excel.. got to Formula -> Name Manager -> New

Excelchat Expert
10/08/2018 - 08:15

in the small popup window..

Excelchat Expert
10/08/2018 - 08:16

provide the Name = cColor and for refers to = =GET.CELL(63,!B2)

Excelchat Expert
10/08/2018 - 08:17

here B2 is the cell where you need to get the # for the cell color..

Excelchat Expert
10/08/2018 - 08:17

And A2 is the cell which has some color

User
10/08/2018 - 08:17

then do I need to do this for every color on the sheet?

Excelchat Expert
10/08/2018 - 08:18

extend the formula to the adjecent cell.. and you will get some number..

Excelchat Expert
10/08/2018 - 08:18

then could the cell which has numbers greater than 0.. and that would be the could of cells which has colors in col A

Excelchat Expert
10/08/2018 - 08:19

You need not do this for every color..

User
10/08/2018 - 08:19

but B2 has a color that's different than any other cell in the sheet

User
10/08/2018 - 08:19

as does A2

Excelchat Expert
10/08/2018 - 08:20

the color of B2 is white and result will be 0

Excelchat Expert
10/08/2018 - 08:21

any color apart from the default white will be tagged with a number.

User
10/08/2018 - 08:21

so why is B2 the cell that's referenced in the formula?

Excelchat Expert
10/08/2018 - 08:22

B2 is just a staring point for the formula in GET.CELL..

Excelchat Expert
10/08/2018 - 08:22

it can vary to any cell address based on your requirement..

User
10/08/2018 - 08:23

and in the file you sent me, why was B2 used as the starting point please?

User
10/08/2018 - 08:23

why not A2 where the colors are?

Excelchat Expert
10/08/2018 - 08:24

b'cos A is the column where the color will be so I cannot use A column..

Excelchat Expert
10/08/2018 - 08:24

the adjacent column is where i will get the result .. hence Column B was used..

User
10/08/2018 - 08:25

so this formula is only going to check for cells to the left for color?

Excelchat Expert
10/08/2018 - 08:25

Row 2 was used as... usually users will have 1st row for heading..

Excelchat Expert
10/08/2018 - 08:25

yes..

User
10/08/2018 - 08:26

hmmm...

User
10/08/2018 - 08:26

i have a table spanning A2 to T130

User
10/08/2018 - 08:27

at the top of each column, I need a count of how many cells in that column are colored

User
10/08/2018 - 08:28

or "not white"

Excelchat Expert
10/08/2018 - 08:28

to extend the same solution to your excel..

User
10/08/2018 - 08:28

yes please :)

Excelchat Expert
10/08/2018 - 08:29

build a same name as i explained earlier with reference to Cell B2.

Excelchat Expert
10/08/2018 - 08:29

then add a dummy column next to each of your column with contents..

Excelchat Expert
10/08/2018 - 08:30

use the same formula =cColor.. and get the number in the dummy column..

User
10/08/2018 - 08:30

oh yeah, that could definitely work

Excelchat Expert
10/08/2018 - 08:30

once this is done.. in cell where you want to get the count of colored cell.. use the countif formula..

Excelchat Expert
10/08/2018 - 08:31

once this is done.. hide your dummy column..

Excelchat Expert
10/08/2018 - 08:31

:)

User
10/08/2018 - 08:31

yeah, I think that will work!

Excelchat Expert
10/08/2018 - 08:31

Good luck..

User
10/08/2018 - 08:31

thank you!

Excelchat Expert
10/08/2018 - 08:31

please end the session by clicking on DONE..

User
10/08/2018 - 08:31

sure thing

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*