count cells using multiple criteria

Can anyone show me how to count the number of rows containing two or more
criteria? For example, if one column has the text "DO" and another column has
the text "RD11" how do I count only those rows that contain both DO and RD11?

Hi
I reckon SUMPRODUCT is what you need. Something like
=SUMPRODUCT(--(A2:A1000="DO")*--(B2:B1000="RD11"))

--
Andy.


"Alex68" <[email protected]> wrote in message
news:[email protected]...
> Can anyone show me how to count the number of rows containing two or more
> criteria? For example, if one column has the text "DO" and another column
> has
> the text "RD11" how do I count only those rows that contain both DO and
> RD11?

One way:

=SUMPRODUCT(--(A1:A1000="DD"),--(B1:B1000="RD11"))

In article <[email protected]>,
Alex68 <[email protected]> wrote:

> Can anyone show me how to count the number of rows containing two or more
> criteria? For example, if one column has the text "DO" and another column has
> the text "RD11" how do I count only those rows that contain both DO and RD11?

one method would be to use sum product
=sumproduct(--(A1-A1000="DO"),--(B1-B1000="RD11"))

the "=" in parenthesis makes the results of the parenthesis be true or false
the "--(" changes the true or false to a 1 or 0
you must have equal ranges for each segment in sumproduct and can not use
entire columns or rows listed as "A:A" etc.

"Alex68" wrote:

> Can anyone show me how to count the number of rows containing two or more
> criteria? For example, if one column has the text "DO" and another column has
> the text "RD11" how do I count only those rows that contain both DO and RD11?

There's no need to use -- if you're going to multiply the arrays before
you pass the result to SUMPRODUCT.

See

http://www.mcgimpsey.com/excel/doubleneg.html




In article <[email protected]>, <Andy B> wrote:

> I reckon SUMPRODUCT is what you need. Something like
> =SUMPRODUCT(--(A2:A1000="DO")*--(B2:B1000="RD11"))