2 criteria for a COUNTIF formula?

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

Guest

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)
 
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.
 
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)
 
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
 
Post me your workbook

bob dot phillips at tiscali dot co dot uk

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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.
 
Back
Top