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.
