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" wrote in message
...
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 ,
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?

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 , <Andy B wrote:

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