2 criteria for a COUNTIF formula?

[aaronwexler]

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?

 

[Bob Phillips]

=SUMPRODUCT(--(Sheet1!D2:F65536>0),--(Sheet1!B2:B65536=1))

--

HTH

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

 

[aaronwexler]

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]

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

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

--

HTH

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

 

[aaronwexler]

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]

Post me your workbook

bob dot phillips at tiscali dot co dot uk

--

HTH

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

 

[robot]

aaronwelxer,

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

=SUMPRODUCT(--(Sheet1!D265536>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.