Counting Cell Background Colours

  • Thread starter Thread starter Sam Harman
  • Start date Start date
S

Sam Harman

Hi I have a spreadsheet which has some conditional formatting and
therefore some cells are coloured green, orange or yellow.

Is there a way to count these cells and produce a total number.

For example if row one has 20 columns and 6 of them are coloured
(either green, yellow or orange) can I have a new column at 21 to show
the total number of coloured cells = 6?

Any help appreciated

Thanks

Sam
 
hi Sam,

this is possible, by using the condition that was used to do the conditional format
 
Sam Harman said:
Hi I have a spreadsheet which has some conditional formatting and
therefore some cells are coloured green, orange or yellow.

Is there a way to count these cells and produce a total number.

For example if row one has 20 columns and 6 of them are coloured
(either green, yellow or orange) can I have a new column at 21 to show
the total number of coloured cells = 6?

Any help appreciated

Thanks

Sam
Click to expand...


Maybe something like

=SUMPRODUCT(--(A2:T2="Pen")+--(A2:T2>=10)+--(A2:T2=5))

in U2?

More generally,

=SUMPRODUCT(--(A2:T2=condition1)+--(A2:T2=condition2)+--(A2:T2=condition3)+...)

More info on Debra Dalgleish's blog -
http://blog.contextures.com/archives/2009/06/17/excel-for-underdogs/

I recommend her link to Bob Phillip's write-up.
 
Maybe something like

=SUMPRODUCT(--(A2:T2="Pen")+--(A2:T2>=10)+--(A2:T2=5))

in U2?

More generally,

=SUMPRODUCT(--(A2:T2=condition1)+--(A2:T2=condition2)+--(A2:T2=condition3)+...)

More info on Debra Dalgleish's blog -
http://blog.contextures.com/archives/2009/06/17/excel-for-underdogs/

I recommend her link to Bob Phillip's write-up.
Click to expand...

Hi and thanks for all of your replies and advice.

Clare, if I use your sumprodicy suggestion, what do i use as the
condition as I have tried putting in green, yellow etc but I get an
error and no result....

Any help appreciated

Thanks

Sam
 
Hi I have a spreadsheet which has some conditional formatting and
therefore some cells are coloured green, orange or yellow.

Is there a way to count these cells and produce a total number.

For example if row one has 20 columns and 6 of them are coloured
(either green, yellow or orange) can I have a new column at 21 to show
the total number of coloured cells = 6?

Any help appreciated

Thanks

Sam
Click to expand...

The free add-in ASAP Utilities has a function to evaluate color index of
a cell's fill
 
The free add-in ASAP Utilities has a function to evaluate color index of
a cell's fill
Click to expand...


Thanks Zaidy but could you expand please....i have ASAP installed but
have no idea how to use it to count the number of cells that have a
cell background colour

Thanks

Sam
 
Thanks Zaidy but could you expand please....i have ASAP installed but
have no idea how to use it to count the number of cells that have a
cell background colour

Thanks

Sam
Click to expand...
Here is a function I have used , once for each color:
- the cell color index was taken from a "sample" cell I had set up

=ASAPSUMBYCELLCOLOR(AM$4:AM$171,ASAPCELLCOLORINDEX($AO220))
 
Sam Harman said:
Hi and thanks for all of your replies and advice.

Clare, if I use your sumprodicy suggestion, what do i use as the
condition as I have tried putting in green, yellow etc but I get an
error and no result....

Any help appreciated

Thanks

Sam
Click to expand...


To answer your question, each SUMPRODUCT condition needs to be identical
to the conditional format condition that determines each distinct
background color.

Since you have the ASAP utilities, I think it would be far easier to use
Zaidy's suggestion.
 
Cliff

I haven't had ASAP installed for a long time, but the feature Zaidy is
speaking of only returned color indexes of manually backgrounded
cells.

i.e. no good for CF colored cells.

Perhaps Zaidy could confirm yea or nay about newer version or ASAP


Gord Dibben Microsoft Excel MVP
 
Only code I have ever seen that calculates CF rules and returns a
count of conditions is that by Chip Pearson.

http://www.cpearson.com/excel/CFColors.htm

There may be more out there..............I just don't know where.


Gord
Click to expand...

I am using the current version 4.7.2 (April 2011)

I have never applied the function to CF cells but only to ones I have
manually set.

May work if recalc twice in succession.
 
I am using the current version 4.7.2 (April 2011)

I have never applied the function to CF cells but only to ones I have
manually set.

May work if recalc twice in succession.
Click to expand...

Thanks everyone for all your help and assistance on this....i am not
an excel expert so wanted soemthing like an idiots guide lol.......

I did however manage to find the answer to my question purely bu
chance and I have reporduced it below for info....
There are no built-in functions to use in a formula that can detect the "filled in" color of a cell. You would have to use a custom made function like below.

If you want to count the cells with filled in background color in say range A1 to B20

=CountFilled(A1:B20)

To install this custom function:
Alt+F11 to open the VB editor
On the menu select Insert\Module
Paste the code below in the VB edit window

---

Function CountFilled(rng As Range) As Long
Dim Cell As Range
Application.Volatile True
For Each Cell In rng
If Cell.Interior.ColorIndex <> -4142 Then
CountFilled = CountFilled + 1
End If
Next Cell
End Function
Click to expand...

I have another challenge if you are up for it lol

I have a list of numbers which I would like to automatically format
with a green background......does anyone know how I can do this. The
start of the top four values will always be indicated by rthe last
number in the set being greater than the first number in the next
set.....in the first example below rthe set ends at 16 as the next
number is 0.8 etc

3.5
4
5
8
8 the first four values should be highlighted green - so in this
case it would be 5 numbers as these are the first four values, 8 being
selected twice
10
12
14
14
14
16
0.8 the next four value should be highlighted green
4
6.5
8
14
16

the next four and so on..........the top four values are identified by
the number to start with being smaller than the preceding number

1.2
3
3.5
8
12
2.5 From here top four values to be green
3
3.5
5.5
7.5
10
20
2.75 From here top four values to be green
3.33
5
6.5
2.75 From here top four values to be green
3
4.5
6
8
10
10
33
2.75 From here top four values to be green
3.25
5.5
10
10
12
12
12
14
16
2 From here top four values to be green
7
4.5
6.5
8
10
10

I hope this makes sense and any help appreciated - i could send the
spreadsheet if it helps

thanks

Sam
 
[ ]
Thanks everyone for all your help and assistance on this....i am not
an excel expert so wanted soemthing like an idiots guide lol.......

I did however manage to find the answer to my question purely bu
chance and I have reporduced it below for info....
(snip)
Click to expand...

Back to my original suggestion of using SUMPRODUCT.
I have another challenge if you are up for it lol
Click to expand...
(snip)
I seem to recall seeing this question under a different subject ....
please monitor that thread for responses, rather than introducing a new
subject under the same thread topic. Introducing unrelated topics tends
to make for confusing archive serching.
 
[ ]
Thanks everyone for all your help and assistance on this....i am not
an excel expert so wanted soemthing like an idiots guide lol.......

I did however manage to find the answer to my question purely bu
chance and I have reporduced it below for info....
(snip)
Click to expand...

Back to my original suggestion of using SUMPRODUCT.
I have another challenge if you are up for it lol
Click to expand...
(snip)
I seem to recall seeing this question under a different subject ....
please monitor that thread for responses, rather than introducing a new
subject under the same thread topic. Introducing unrelated topics tends
to make for confusing archive serching.
Click to expand...

Yes you did lol but I didn't receive a solution and therefore i
thought i would open it to this thread as there were a number of
people contributing......apologies if that is not he done thing :(

Sam
 
Sam Harman said:
[ ]>>>>
Note: this doesn't count cells that have background colored by
Conditional Formatting.
If your cells are colored using Conditional Formatting, you should
use
the same
conditions used in Conditional Formatting within a formula to count
those cells.
Click to expand...

Back to my original suggestion of using SUMPRODUCT.
I have another challenge if you are up for it lol
Click to expand...
(snip)
I seem to recall seeing this question under a different subject ....
please monitor that thread for responses, rather than introducing a
new
subject under the same thread topic. Introducing unrelated topics
tends
to make for confusing archive serching.
Click to expand...

Yes you did lol but I didn't receive a solution and therefore i
thought i would open it to this thread as there were a number of
people contributing......apologies if that is not he done thing :(

Sam
Click to expand...

Not a problem (the first time) <grin>.
 
Here is a function I have used , once for each color:
- the cell color index was taken from a "sample" cell I had set up

=ASAPSUMBYCELLCOLOR(AM$4:AM$171,ASAPCELLCOLORINDEX($AO220))
Click to expand...
I'm a newbie and just installed ASAP into Excel 2007

If I put the sample color in A171,
and wanted to count all the cells that:

a) had the "A171 sample color" in cells B2 to B151
- with the total count in cell B171
b) did _not_ have the "A171 sample color"
- with the total count in cell B172

What formula should I use and where do I put it?

Thank you.

Jeff
 
I'm a newbie and just installed ASAP into Excel 2007

If I put the sample color in A171,
and wanted to count all the cells that:

a) had the "A171 sample color" in cells B2 to B151
- with the total count in cell B171
b) did _not_ have the "A171 sample color"
- with the total count in cell B172

What formula should I use and where do I put it?

Thank you.

Jeff
Click to expand...
A171 goes in the "Index" as ASAPCELLCOLORINDEX($A171),. or, for (b),
make any cell with the color and use the same function in another cell
to find the index.
Example: Sample color in A1 then in B1 use =ASAPCELLCOLORINDEX(A1) and
use the resulting number in place of ASAPCELLCOLORINDEX($AO220)
then can delete A1 and B1

Note that the function produces the sum of the range (AM$4:AM$171) so if
you want to count the cells need to be more complicated or make a
companion cell with a 1 in it with the same color. This can be a hidden
column.

I suggest in your spread sheet click on an empty cell, then on the "Fx"
in one of the command lines on top and then select the ASAP function
from the list. A help & explanation window will open and should answer
your questions.
 
A171 goes in the "Index" as ASAPCELLCOLORINDEX($A171),. or, for (b),
make any cell with the color and use the same function in another cell
to find the index.
Example: Sample color in A1 then in B1 use =ASAPCELLCOLORINDEX(A1) and
use the resulting number in place of ASAPCELLCOLORINDEX($AO220)
then can delete A1 and B1

Note that the function produces the sum of the range (AM$4:AM$171) so if
you want to count the cells need to be more complicated or make a
companion cell with a 1 in it with the same color. This can be a hidden
column.

I suggest in your spread sheet click on an empty cell, then on the "Fx"
in one of the command lines on top and then select the ASAP function
from the list. A help & explanation window will open and should answer
your questions.
Click to expand...
Thank you.
 
Back
Top