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:F65536>0),--(Sheet1!B2:B65536=1))

--

HTH

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


"aaronwexler" <[email protected]> wrote in message
news:[email protected]...
> 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:F65536>0),--(Sheet1!B2:B65536=1))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "aaronwexler" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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:F65536>0),--(Sheet1!B2:B65536=1))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "aaronwexler" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:F65536>0),--(Sheet1!B2:B65536=1))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "aaronwexler" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:F65536>0),--(Sheet1!B2:B65536=1))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "aaronwexler" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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:D65536>0),--(Sheet1!B2:B65536=1)) +
SUMPRODUCT(--(Sheet1!E2:E65536>0),--(Sheet1!B2:B65536=1)) +
SUMPRODUCT(--(Sheet1!F2:F65536>0),--(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.