=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
|