# Excel - COLUMN Function Problem - Expert Solution

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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at