writing a formula for a colored value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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?
 
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
 
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)
 
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)
 
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?
 
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?
 
=COUNTIF(Sheet1!B2:B65536,1)

no values are needed to sum

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Bob, That worked well.

Bob Phillips said:
=COUNTIF(Sheet1!B2:B65536,1)

no values are needed to sum

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top