2 criteria for a COUNTIF formula?
 

I was wondering if it was possible to use 2 criteria for a count if formula.
I want to count the number of numbers in a range that are above 0. I would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count the number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the range
D2:F65536?

=SUMPRODUCT(--(Sheet1!D2:F655360),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" wrote in message
...
I was wondering if it was possible to use 2 criteria for a count if
formula.
I want to count the number of numbers in a range that are above 0. I
would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that
corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count the
number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the range
D2:F65536?

Thank you again Bob but when I plug this formula in I get a value error. It
looks right though so I'm not sure what isnt working.

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!D2:F655360),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" wrote in message
...
I was wondering if it was possible to use 2 criteria for a count if
formula.
I want to count the number of numbers in a range that are above 0. I
would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that
corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count the
number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the range
D2:F65536?

Sorry, cut and pasted the ranges and missed that one covered two columns.
Try this

=SUMPRODUCT(--((Sheet1!D2:D65536+Sheet1!F2:F65536)0),--(Sheet1!B2:B65536=1)
)

--

HTH

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


"aaronwexler" wrote in message
...
Thank you again Bob but when I plug this formula in I get a value error.
It
looks right though so I'm not sure what isnt working.

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!D2:F655360),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" wrote in message
...
I was wondering if it was possible to use 2 criteria for a count if
formula.
I want to count the number of numbers in a range that are above 0. I
would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that
corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count the
number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the
range
D2:F65536?

Thanks Bob but the value I get is still zero and thats not right. :( but the
formula looks good to me so I still am not sure whats wrong

"Bob Phillips" wrote:

Sorry, cut and pasted the ranges and missed that one covered two columns.
Try this

=SUMPRODUCT(--((Sheet1!D2:D65536+Sheet1!F2:F65536)0),--(Sheet1!B2:B65536=1)
)

--

HTH

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


"aaronwexler" wrote in message
...
Thank you again Bob but when I plug this formula in I get a value error.
It
looks right though so I'm not sure what isnt working.

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!D2:F655360),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" wrote in message
...
I was wondering if it was possible to use 2 criteria for a count if
formula.
I want to count the number of numbers in a range that are above 0. I
would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that
corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count the
number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the
range
D2:F65536?

Post me your workbook

bob dot phillips at tiscali dot co dot uk

--

HTH

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


"aaronwexler" wrote in message
...
Thanks Bob but the value I get is still zero and thats not right. :( but
the
formula looks good to me so I still am not sure whats wrong

"Bob Phillips" wrote:

Sorry, cut and pasted the ranges and missed that one covered two
columns.
Try this


=SUMPRODUCT(--((Sheet1!D2:D65536+Sheet1!F2:F65536)0),--(Sheet1!B2:B65536=1)
)

--

HTH

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


"aaronwexler" wrote in message
...
Thank you again Bob but when I plug this formula in I get a value
error.
It
looks right though so I'm not sure what isnt working.

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!D2:F655360),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" wrote in
message
...
I was wondering if it was possible to use 2 criteria for a count
if
formula.
I want to count the number of numbers in a range that are above 0.
I
would
write that like:

=COUNTIF(Sheet1!D2:F65536,"0")

I also have the "color" coded by using a number in column B that
corisponds
to the color ie, 1=yellow 2=green and 3=red. If I want to count
the
number
of yellow values in a range I would use the formula:

=COUNTIF(Sheet1!B2:B65536,1)

Is it possible to count all of the possitive yellow values in the
range
D2:F65536?

aaronwelxer,

Along Bob's line, and as SUMPRODUCT allows only arrays of the same sizes,
the following formula ought to work:

=SUMPRODUCT(--(Sheet1!D2:D655360),--(Sheet1!B2:B65536=1)) +
SUMPRODUCT(--(Sheet1!E2:E655360),--(Sheet1!B2:B65536=1)) +
SUMPRODUCT(--(Sheet1!F2:F655360),--(Sheet1!B2:B65536=1))

Of course this formula leaves a lot to be desired because when your range
grows and includes more columns, the formula needs to be modified
correspondingly. In the meantime, hope this will serve the purpose.