+ Reply to Thread
Results 1 to 5 of 5

count cells using multiple criteria

  1. #1
    Alex68
    Guest

    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. #2
    Guest

    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. #3
    JE McGimpsey
    Guest

    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. #4
    bj
    Guest

    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. #5
    JE McGimpsey
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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