count cells using multiple criteria

[Guest]

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?

 

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[Guest]

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.

 

[JE McGimpsey]

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