Counting Cell Values that Meet Certain Criteria

[David Seibel]

The following example illustrates what I would like to do:

There are 2 columns of data: column A with types of fruit and column
with color (apple red, apple green, banana yellow, banana green, etc.)

I would like to count all apples that are red (i.e., count column b
"red" while column A = "apples")

I believe that I have to use the countif function but haven't found ou
how or even if it is possible to condition the count based on values i
another column.

Don't know if the and function comes into play and if it does how t
use it.

Thanks for any assistance you may be able to provide

 

[Frank Kabel]

Hi
COUNTIF can only handle one condition. You may use
=SUMPRODUCT((A1:A100="apple")*(B1:B100="red"))

or as an alternative syntax
=SUMPRODUCT(--(A1:A100="apple"),--(B1:B100="red"))

 

[lindasf]

Hi Frank,

I tried this also, but got a value of zero (0), which is incorrect.

Any ideas? Thx much. lindasf


=SUMPRODUCT(($D$1:$CX$1="*2*")*($D2:$CX2="*x*"))

Please see cell DC4.

P.S. Pls. also see cell DC2 for an array value that I tried that als
returned a value of zero

Attachment filename: junk-end user - sorted by curriculum 1 & alpha subsystem - merged &sorted - 3 columns.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=49699

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(ISNUMBER(FIND("2",$D$1:$CX$1))*($D2:$CX2="x"))

 

[lindasf]

Vielen Dank Frank!

It works great !

lindasf

 

[Frank Kabel]

Hi
thanks for the feedback