counting colored data

[lmiller]

I have a spread sheet where if a number doesn't fall within certain limits
the font is red representing a NG part. If the number falls within certain
limits the font is green representing a good part. I would like to know if
there is a way to count the # of NG(or red numbers). I tried copying the
spreadsheet to my question but the fonts don't come over in color. There are
numerous cells and would be much easier to count if there was a formula to do
so. I know how to make a count formula but not one if the font is a certain
color.

 

[Luke M]

Rather than counting color (which you can't do w/o VB), count the cells
using same criteria as your conditional formats.
e.g.
=SUMPRODUCT(--(A2:A100>0),--(A2:A100<=10))

Would count everything within A2:A100 that is greater than zero and less
than or equal to 10.

other example:
=SUMPRODUCT((A2:A100<0)+(A2:A100>10))

Would count everything within A2:A100 that is less than 0 or greater than
10.

 

[Mike H]

Hi,

You can't count the colours with a worksheet function but you can get the
count you want by directly checking the numbers. tell us the number limits
and someone will help
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Eduardo]

Hi,
See CPearson web for counting colors

http://www.cpearson.com/excel/colors.aspx

 

[Gord Dibben]

Are the coloprs the result of Conditional Formatting?

Count based on those conditions.


Gord Dibben MS Excel MVP

 

[lmiller]

what?

Hi,
See CPearson web for counting colors

http://www.cpearson.com/excel/colors.aspx

 

[lmiller]

Thanks Mike,

Each cell has a conditional format. I posted an excert from my worksheet
but unfortunatly the data didn't come over color coded for you to see. For
sake of argument let say the first column is red and the remaining colums are
green. Each cell has a conditional format. If the # doesn't falls between
certain limits it prints in red. If it does fall between certain number it
prints in green. Instead of manually counting all the red numbers, I
wanted to try and do a count based on my conditional format. Each row has
it's own limits and the worksheet contains about 100 rows but I need an
overall "red" count. It's quite cumbersome to manually count and I thought
the formual would be extremely long.

0.180 0.184 0.113 0.111
0.173 0.177 0.113 0.119
0.846 0.751 0.075 0.050

 

[lmiller]

Luke

I have thought about what you have suggested. Below is just an exert from
my worksheet. Each row has it's own limit to tell whether the number should
print a certain way. For example row #1 if # falls between .10 and .15
print in green if not print red. row #2 if # falls bewteen .00 and .10
print green if not print red.

My formula to write would take just as long than to manually count. Unless
I am looking at it totally wrong. Any other suggestions or help?

Thank you so much!!!!!

0.173 0.177 0.113 0.119 0.119 0.116
0.846 0.751 0.075 0.050 0.078 0.082
0.626 0.640 0.031 0.060 0.072 0.074
3.799 4.347 1.187 0.903 1.488 1.118
3.520 3.913 2.593 2.638 1.824 2.049
0.267 0.312 0.263 0.316 0.281 0.247
0.336 0.351 0.271 0.279 0.219 0.204
-0.075 -0.119 -0.349 -0.334 -0.061 -0.180
1.557 1.460 1.318 1.276 1.405 1.400
0.264 0.272 0.282 0.269 0.272 0.266
0.296 0.309 0.286 0.298 0.304 0.289
0.431 0.441 0.457 0.297 0.444 0.440
0.395 0.593 0.418 0.550 0.564 0.489
0.605 0.536 0.374 0.415 0.559 0.519
0.456 0.474 0.435 0.382 0.506 0.377
0.894 0.860 0.885 0.864 0.878 0.882
0.154 -0.093 0.139 0.089 0.082 0.147
0.728 0.767 0.749 0.729 0.714 0.648
0.680 0.668 0.688 0.670 0.691 0.690
0.240 0.031 0.180 0.064 0.079 0.171
0.731 0.901 0.793 0.755 0.788 0.639
0.934 1.017 0.893 0.809 0.798 0.796
-0.156 -0.055 -0.118 0.311 0.076 -0.006
-0.540 -0.758 -0.463 -0.503 -0.501 -0.529
0.654 0.385 0.402 0.486 0.442 0.492
1.786 1.212 1.914 1.751 2.050 1.416
-1.731 -1.760 -1.012 -1.167 -0.893 -0.736
3.790 3.711 1.290 1.550 1.210 1.281
-0.403 -0.432 -0.568 -0.660 -0.550 -0.357
0.546 0.714 0.271 0.298 0.247 0.221
0.319 0.494 0.233 0.247 0.178 0.184
-1.731 -1.760 -1.012 -1.167 -0.893 -0.736
-0.013 -0.018 -0.007 -0.009 -0.006 -0.004

 

[Mike H]

Hi,

I still think this can be done with a formula but you didn't give us all the
number ranges.

If you want to count conditional format colours then it can be done but it
isn't easy. Bob Philips has a solution here

http://www.xldynamic.com/source/xld.CFConditions.html

If you don't fancy this route then post the number limits for each row and
someone will work out a solution.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Luke M]

Doyou have those boundaries called out somewhere in the row, or did you
manually input them for every row?

I'll assume your criteria are in column A and B, and you've colored cells in
C:G

=SUMPRODUCT(--(C2:G100>A2:A100),--(C2:G100<B2:B100))