Counting Cell Values that Meet Certain Criteria

  • Thread starter Thread starter David Seibel
  • Start date Start date
D

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
 
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"))
 
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
 
Back
Top