# count cells using multiple criteria

1. ## 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?

2. ## Re: count cells using multiple criteria

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

--
Andy.

"Alex68" <Alex68@discussions.microsoft.com> wrote in message
news:0F41177E-45A0-4B0B-AE33-6BCCF2F3B8D1@microsoft.com...
> 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?

3. ## Re: count cells using multiple criteria

One way:

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

In article <0F41177E-45A0-4B0B-AE33-6BCCF2F3B8D1@microsoft.com>,
Alex68 <Alex68@discussions.microsoft.com> 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?

4. ## RE: count cells using multiple criteria

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?

5. ## Re: count cells using multiple criteria

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 <uNv1AxHYFHA.3164@TK2MSFTNGP09.phx.gbl>, <Andy B> wrote:

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1