CountIF

[Guest]

I want to use countif and only want the cases counted if conditions in two
ranges are met (For example: =COUNTIF(C:C,"CJ") AND (D,"Yes")), but I can't
figure out how to make this function evaluate two separate ranges.

I saw someone write something before about using product, but I don't want
to multiply. I also saw something about add, but I don't want to add because
then each case counts twice.

THANKS

bnagbob

 

[duane]

this is so popular it would be nice if excel put it in the sumif an
countif help in the future. Frank, what do you think?

=sumproduct((C1:C1000="CJ")*(D11000="Yes"))

if 1-1000 rows are not sufficient, increase

 

[Myrna Larson]

YOu can't do it with two criteria. The function was not written to handle
that. It doesn't matter that you "don't want to multiply". SUMPRODUCT or an
array formula that multiplies is what's required here.

=SUMPRODUCT(--(C:C="CJ"),--(D="yes"))

Try it an see if it doesn't give you the right answer.

 

[Guest]

Thanks for the help! It worked!!! What about if some cells in the D range
have dates and others are blank and I only want the items counted that are CJ
and have a date?

Something like:
=sumproduct((C1:C1000="CJ")*(D11000="if there is a date"))

THANK YOU VERY MUCH!!!

 

[duane]

if the date is a date and not text

Something like:

=sumproduct((C1:C1000="CJ")*(D11000>0)