counting colored data
 

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.

counting colored data
 

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:A1000),--(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:A10010))

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

Luke M
"lmiller" wrote in message
...
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.

counting colored data
 

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.


"lmiller" wrote:

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.

counting colored data
 

Hi,
See CPearson web for counting colors

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

"lmiller" wrote:

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.

counting colored data
 

Are the coloprs the result of Conditional Formatting?

Count based on those conditions.


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 07:45:02 -0700, lmiller
wrote:

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.

counting colored data
 

what?

"Eduardo" wrote:

Hi,
See CPearson web for counting colors

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

"lmiller" wrote:

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.

counting colored data
 

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


"Mike H" wrote:

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.


"lmiller" wrote:

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.

counting colored data
 

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


"Luke M" wrote:

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:A1000),--(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:A10010))

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

Luke M
"lmiller" wrote in message
...
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.


.

counting colored data
 

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.


"lmiller" wrote:

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


"Mike H" wrote:

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.


"lmiller" wrote:

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.

counting colored data
 

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:G100A2:A100),--(C2:G100<B2:B100))

--
Best Regards,

Luke M
"lmiller" wrote in message
...
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


"Luke M" wrote:

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:A1000),--(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:A10010))

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

Luke M
"lmiller" wrote in message
...
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.


.