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 here:

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

In article <[email protected]>,
"aaronwexler" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 here:
>
> http://cpearson.com/excel/colors.htm
>
> In article <[email protected]>,
> "aaronwexler" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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(rng>0,rng))

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

--

HTH

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


"aaronwexler" <[email protected]> wrote in message
news:[email protected]...
> 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 here:
> >
> > http://cpearson.com/excel/colors.htm
> >
> > In article <[email protected]>,
> > "aaronwexler" <[email protected]> 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))+(SUMIF(Sheet1!I2:I65536,1,Sheet1!K2:M65536))+(SUMIF(Sheet1!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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > > >
> > >
> > >
> > >
>
>
>