writing a formula for a colored value
 

I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

Take a look he

http://cpearson.com/excel/colors.htm

In article ,
"aaronwexler" wrote:

I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

See http://www.xldynamic.com/source/xld.ColourCounter.html, but note the
constraints

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info in
a
range of cells. I have a range of cells I want to include in a formula
but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for
data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values in
this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

Thanks it looks like that might help on the color issue if I can make heads
and tails of it. Do you have any info on how to do the same thing but with
possitive or negative number. For example I want to write a function that
will only take into account the possitive or negative numbers in a range of
cells.

"JE McGimpsey" wrote:

Take a look he

http://cpearson.com/excel/colors.htm

In article ,
"aaronwexler" wrote:

I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

Thanks for that website, but I tried to ues the formulas there and I just get
errors. Is there something else I have to do other than use those formulas?

"Bob Phillips" wrote:

See http://www.xldynamic.com/source/xld.ColourCounter.html, but note the
constraints

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info in
a
range of cells. I have a range of cells I want to include in a formula
but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for
data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values in
this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

Aaron,

Instead of coloring the cell, put a 1 in the cell next to the value, then use SUMIF:

=SUMIF(A1:A100,1,B1:B100)

will sum the values from B1:B100 where the corresponding value in A1:A100 is 1.

For the count of negative, use

=COUNTIF(B1:B100,"<0")

You could format column A to hide the numbers (or make it very narrow) and still use the yellow to
highlight, for prinouts etc.

HTH,
Bernie
MS Excel MVP


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

You need to copy the UDF provided into a standard code module in the VBE.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
Thanks for that website, but I tried to ues the formulas there and I just
get
errors. Is there something else I have to do other than use those
formulas?

"Bob Phillips" wrote:

See http://www.xldynamic.com/source/xld.ColourCounter.html, but note the
constraints

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific
info in
a
range of cells. I have a range of cells I want to include in a
formula
but I
want the formula to only include the values that I have colored
Yellow. I
could select each yellow one by hand but I want this formula to work
for
data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values
in
this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the
negative
numbers. For example in the range =COUNT(C2:E65536) how could I write
the
formula to only include the negative or possitive numbers?

Thanks Aaron

As an example, sum them

=SUM(IF(rng0,rng))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
Thanks it looks like that might help on the color issue if I can make
heads
and tails of it. Do you have any info on how to do the same thing but
with
possitive or negative number. For example I want to write a function that
will only take into account the possitive or negative numbers in a range
of
cells.

"JE McGimpsey" wrote:

Take a look he

http://cpearson.com/excel/colors.htm

In article ,
"aaronwexler" wrote:

I have a question about how to write a formula to include specific
info in a
range of cells. I have a range of cells I want to include in a
formula but I
want the formula to only include the values that I have colored
Yellow. I
could select each yellow one by hand but I want this formula to work
for data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values
in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the
negative
numbers. For example in the range =COUNT(C2:E65536) how could I write
the
formula to only include the negative or possitive numbers?

Thanks Aaron

Thanks Bernie for the info the 0 in the formula worked well, but I cant seem
to get the SUMIF to work right. It is giving me a number but it isn't the
right number. Here is how I have the formula written.

=SUMIF(Sheet1!B2:B65536,1,Sheet1!D2:F65536)

B is the column I have the number identifying the color in and the D2:F65536
is where the rage that includes the data I want to sum.

I am getting the number -19 when I should be getting 25. I also want to add
the same info from other colums I have so I wrote this.

=SUMIF(Sheet1!B2:B65536,1,Sheet1!D2:F65536))+(SUMI F(Sheet1!I2:I65536,1,Sheet1!K2:M65536))+(SUMIF(She et1!W2:W65536,1,Sheet1!Y2:AA65536))

Can you see anything that is wrong with that?


"Bernie Deitrick" wrote:

Aaron,

Instead of coloring the cell, put a 1 in the cell next to the value, then use SUMIF:

=SUMIF(A1:A100,1,B1:B100)

will sum the values from B1:B100 where the corresponding value in A1:A100 is 1.

For the count of negative, use

=COUNTIF(B1:B100,"<0")

You could format column A to hide the numbers (or make it very narrow) and still use the yellow to
highlight, for prinouts etc.

HTH,
Bernie
MS Excel MVP


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

Is there a way to do the countif formula like the sumif formula? For example
I want to know how many values are associated with orage. I coded the orange
with the number 1 in column B. SO my sumif formulas look like

=SUMIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

I tried to do the same thing with the countif and it looks like this:

=COUNTIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

But when I do that I get an error: "YOU ARE ENTERING TO MANY ARGUMENTS" Is
it possible to count the values I have associated with a color the way I did
with the sumif?

"Bernie Deitrick" wrote:

Aaron,

Instead of coloring the cell, put a 1 in the cell next to the value, then use SUMIF:

=SUMIF(A1:A100,1,B1:B100)

will sum the values from B1:B100 where the corresponding value in A1:A100 is 1.

For the count of negative, use

=COUNTIF(B1:B100,"<0")

You could format column A to hide the numbers (or make it very narrow) and still use the yellow to
highlight, for prinouts etc.

HTH,
Bernie
MS Excel MVP


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info in a
range of cells. I have a range of cells I want to include in a formula but I
want the formula to only include the values that I have colored Yellow. I
could select each yellow one by hand but I want this formula to work for data
I continue to add in the future which is way I want to have a large selection
of cells. So for example if I want the sum of all the yellow values in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the negative
numbers. For example in the range =COUNT(C2:E65536) how could I write the
formula to only include the negative or possitive numbers?

Thanks Aaron

=COUNTIF(Sheet1!B2:B65536,1)

no values are needed to sum

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
Is there a way to do the countif formula like the sumif formula? For
example
I want to know how many values are associated with orage. I coded the
orange
with the number 1 in column B. SO my sumif formulas look like

=SUMIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

I tried to do the same thing with the countif and it looks like this:

=COUNTIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

But when I do that I get an error: "YOU ARE ENTERING TO MANY ARGUMENTS"
Is
it possible to count the values I have associated with a color the way I
did
with the sumif?

"Bernie Deitrick" wrote:

Aaron,

Instead of coloring the cell, put a 1 in the cell next to the value,
then use SUMIF:

=SUMIF(A1:A100,1,B1:B100)

will sum the values from B1:B100 where the corresponding value in
A1:A100 is 1.

For the count of negative, use

=COUNTIF(B1:B100,"<0")

You could format column A to hide the numbers (or make it very narrow)
and still use the yellow to
highlight, for prinouts etc.

HTH,
Bernie
MS Excel MVP


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info
in a
range of cells. I have a range of cells I want to include in a
formula but I
want the formula to only include the values that I have colored
Yellow. I
could select each yellow one by hand but I want this formula to work
for data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values
in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the
negative
numbers. For example in the range =COUNT(C2:E65536) how could I write
the
formula to only include the negative or possitive numbers?

Thanks Aaron

Thanks Bob, That worked well.

"Bob Phillips" wrote:

=COUNTIF(Sheet1!B2:B65536,1)

no values are needed to sum

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aaronwexler" wrote in message
...
Is there a way to do the countif formula like the sumif formula? For
example
I want to know how many values are associated with orage. I coded the
orange
with the number 1 in column B. SO my sumif formulas look like

=SUMIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

I tried to do the same thing with the countif and it looks like this:

=COUNTIF(Sheet1!B2:B65536,1,Sheet1!D2:D65536)

But when I do that I get an error: "YOU ARE ENTERING TO MANY ARGUMENTS"
Is
it possible to count the values I have associated with a color the way I
did
with the sumif?

"Bernie Deitrick" wrote:

Aaron,

Instead of coloring the cell, put a 1 in the cell next to the value,
then use SUMIF:

=SUMIF(A1:A100,1,B1:B100)

will sum the values from B1:B100 where the corresponding value in
A1:A100 is 1.

For the count of negative, use

=COUNTIF(B1:B100,"<0")

You could format column A to hide the numbers (or make it very narrow)
and still use the yellow to
highlight, for prinouts etc.

HTH,
Bernie
MS Excel MVP


"aaronwexler" wrote in message
...
I have a question about how to write a formula to include specific info
in a
range of cells. I have a range of cells I want to include in a
formula but I
want the formula to only include the values that I have colored
Yellow. I
could select each yellow one by hand but I want this formula to work
for data
I continue to add in the future which is way I want to have a large
selection
of cells. So for example if I want the sum of all the yellow values
in this
range =SUM(C2:E65536) how could I write the formula to do that?

I would also like to write a formula that would count only the
negative
numbers. For example in the range =COUNT(C2:E65536) how could I write
the
formula to only include the negative or possitive numbers?

Thanks Aaron