+ Reply to Thread
Results 1 to 8 of 8

How to count rows that match two criteria?

  1. #1
    Broida (spamless)
    Guest

    How to count rows that match two criteria?

    Hi!
    I hope the title uses the right terminology, but here's what I am
    trying to do (with greatly simplified example).

    Single workbook. Excel 2002. I'm using two worksheets, but the
    same could apply within a single worksheet.

    In worksheet CCC, column C contains the list of valid strings for
    the AAA!A cells. Each string appears once and only once.

    On worksheet AAA, each cell in column A might contain any ONE of
    several strings (MMM, OOO, PPP, etc) or it might be empty. There
    may be several of each string in this column, and some strings may
    not appear at all.

    On the same worksheet, cells in column B will contain either "Y",
    "N", or be blank.

    I want to have worksheet CCC, column D contain the count of rows
    in AAA where AAA!Ax matches CCC!Cx ---AND--- AAA!Bx == "Y".

    I have tried several functions and some combinations of functions,
    but I can't get past the fact that both AAA!A:A and AAA!B:B might
    contain blank cells. COUNT, COUNTA, DCOUNT, and DCOUNTA don't
    seem to like the "database" on worksheet AAA.

    Example:
    Worksheet AAA:
    Column: A B
    MMM (blank)
    (blank) (blank)
    OOO Y
    MMM Y
    PPP (blank)
    SSS N
    (blank) Y
    OOO Y
    SSS Y

    Worksheet CCC:
    Column: C desired D (count of matches + "Y")
    MMM 1
    NNN 0 (or blank)
    OOO 2
    PPP 0 (or blank)
    QQQ 0 (or blank)
    RRR 0 (or blank)
    SSS 1

    What kind of formula can I plunk into CCC!D:D to get the result
    I'm looking for?? I'm pretty sure it won't be a simple one.

    Thanks for any help/pointers you can give.

    Mike

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hi Mike, the word "concatenate" comes to mind.

    See the post I made a few minutes ago in the Excel Miscellaneous discussion group under the title "Subtotal based on filtered list with more than one condition.

    - Pete

  3. #3
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    =sumproduct((AAA!A1:A1000=CCC!Cx)*(AAA!B1:B1000="Y")*1)

    This will count the number of rows on sheet AAA where column A has an entry
    = to that in the current row of column C on sheet CCC and column B of sheet AAA
    is "Y".

    Note the row range on sheet AAA must be specified.
    not a professional, just trying to assist.....

  4. #4
    Biff
    Guest

    How to count rows that match two criteria?

    Hi!

    This formula will return the 0's:

    =SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))

    1
    0
    2
    0
    0
    0
    1


    This formula will return ""'s in place of 0's:

    =IF(SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    =0,"",SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y")))

    1
    blank
    2
    blank
    blank
    blank
    1


    Or, use the shorter formula and set to not display zero
    values. Tools>Options>View>Zero values.

    Copy whichever formula you choose down column D in CCC!.

    Biff

    >-----Original Message-----
    >Hi!
    > I hope the title uses the right terminology, but

    here's what I am
    > trying to do (with greatly simplified example).
    >
    > Single workbook. Excel 2002. I'm using two

    worksheets, but the
    > same could apply within a single worksheet.
    >
    > In worksheet CCC, column C contains the list of

    valid strings for
    > the AAA!A cells. Each string appears once and

    only once.
    >
    > On worksheet AAA, each cell in column A might

    contain any ONE of
    > several strings (MMM, OOO, PPP, etc) or it might

    be empty. There
    > may be several of each string in this column, and

    some strings may
    > not appear at all.
    >
    > On the same worksheet, cells in column B will

    contain either "Y",
    > "N", or be blank.
    >
    > I want to have worksheet CCC, column D contain the

    count of rows
    > in AAA where AAA!Ax matches CCC!Cx ---AND--- AAA!

    Bx == "Y".
    >
    > I have tried several functions and some

    combinations of functions,
    > but I can't get past the fact that both AAA!A:A

    and AAA!B:B might
    > contain blank cells. COUNT, COUNTA, DCOUNT, and

    DCOUNTA don't
    > seem to like the "database" on worksheet AAA.
    >
    > Example:
    > Worksheet AAA:
    > Column: A B
    > MMM (blank)
    > (blank) (blank)
    > OOO Y
    > MMM Y
    > PPP (blank)
    > SSS N
    > (blank) Y
    > OOO Y
    > SSS Y
    >
    > Worksheet CCC:
    > Column: C desired D (count of matches

    + "Y")
    > MMM 1
    > NNN 0 (or blank)
    > OOO 2
    > PPP 0 (or blank)
    > QQQ 0 (or blank)
    > RRR 0 (or blank)
    > SSS 1
    >
    > What kind of formula can I plunk into CCC!D:D to

    get the result
    > I'm looking for?? I'm pretty sure it won't be a

    simple one.
    >
    > Thanks for any help/pointers you can give.
    >
    > Mike
    >.
    >


  5. #5
    Broida (spamless)
    Guest

    Re: How to count rows that match two criteria?

    Thanks, Biff! I'm trying that right now and having a slight problem.

    Two additional questions.

    I didn't make it clear in my example, but the column A in worksheet AAA
    can be of any length. What I mean is: I don't know where the bottom of
    that column is, and it can change (get longer) at any time. Instead of
    AAA!A$1:A$9, I've tried AAA!A:A, but it doesn't seem to work. Or is
    there another way to say "the whole column"? Remember there are blank
    cells in that column, too, so I can't just go until it hits a blank. I'm
    sure yours would work if the A and B columns were fixed length, but they
    aren't in this case. How do I do a column of unknown length?

    Second: Does that require CTRL+SHIFT+ENTER?? Meaning: Is it an "array
    formula"? I'm not very much "up" on those. Please let me know if that
    is needed/required for this formula. (I've tried it, but it doesn't seem
    to make any difference. I don't think it is needed, but...)

    Here's what I'm putting in right now (modified from your suggestion).
    =IF(SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y"))=0,
    "",SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y")))

    I get #NUM! and the step-by-step seems to indicate the first AAA!A:A
    is the problem.

    Mike


    On Sat, 1 Jan 2005 18:45:20 -0800, Biff <biffinpitt@comcast.net> wrote:

    > Hi!
    >
    > This formula will return the 0's:
    >
    > =SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    >
    > 1
    > 0
    > 2
    > 0
    > 0
    > 0
    > 1
    >
    >
    > This formula will return ""'s in place of 0's:
    >
    > =IF(SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    > =0,"",SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y")))
    >
    > 1
    > blank
    > 2
    > blank
    > blank
    > blank
    > 1
    >
    >
    > Or, use the shorter formula and set to not display zero
    > values. Tools>Options>View>Zero values.
    >
    > Copy whichever formula you choose down column D in CCC!.
    >
    > Biff
    >
    >> -----Original Message-----
    >> Hi!
    >> I hope the title uses the right terminology, but

    > here's what I am
    >> trying to do (with greatly simplified example).
    >>
    >> Single workbook. Excel 2002. I'm using two

    > worksheets, but the
    >> same could apply within a single worksheet.
    >>
    >> In worksheet CCC, column C contains the list of

    > valid strings for
    >> the AAA!A cells. Each string appears once and

    > only once.
    >>
    >> On worksheet AAA, each cell in column A might

    > contain any ONE of
    >> several strings (MMM, OOO, PPP, etc) or it might

    > be empty. There
    >> may be several of each string in this column, and

    > some strings may
    >> not appear at all.
    >>
    >> On the same worksheet, cells in column B will

    > contain either "Y",
    >> "N", or be blank.
    >>
    >> I want to have worksheet CCC, column D contain the

    > count of rows
    >> in AAA where AAA!Ax matches CCC!Cx ---AND--- AAA!

    > Bx == "Y".
    >>
    >> I have tried several functions and some

    > combinations of functions,
    >> but I can't get past the fact that both AAA!A:A

    > and AAA!B:B might
    >> contain blank cells. COUNT, COUNTA, DCOUNT, and

    > DCOUNTA don't
    >> seem to like the "database" on worksheet AAA.
    >>
    >> Example:
    >> Worksheet AAA:
    >> Column: A B
    >> MMM (blank)
    >> (blank) (blank)
    >> OOO Y
    >> MMM Y
    >> PPP (blank)
    >> SSS N
    >> (blank) Y
    >> OOO Y
    >> SSS Y
    >>
    >> Worksheet CCC:
    >> Column: C desired D (count of matches

    > + "Y")
    >> MMM 1
    >> NNN 0 (or blank)
    >> OOO 2
    >> PPP 0 (or blank)
    >> QQQ 0 (or blank)
    >> RRR 0 (or blank)
    >> SSS 1
    >>
    >> What kind of formula can I plunk into CCC!D:D to

    > get the result
    >> I'm looking for?? I'm pretty sure it won't be a

    > simple one.
    >>
    >> Thanks for any help/pointers you can give.
    >>
    >> Mike
    >> .
    >>

    >



  6. #6
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    sumproduct requires a define row range such as a1:a1000

  7. #7
    Broida (spamless)
    Guest

    Re: How to count rows that match two criteria?

    For a dirty workaround, I just used: AAA!A$1:A$10000
    It's ugly, but as long as I don't hit 10,000 entries in
    that column, it works just fine.

    If there is a good way to say "use the whole column"
    (even when there are blanks in the column), please let
    me know.

    Thanks!
    Mike

    On Sun, 02 Jan 2005 12:53:54 -0600, Broida (spamless) <Broida@charter.not.net> wrote:

    > Thanks, Biff! I'm trying that right now and having a slight problem.
    >
    > Two additional questions.
    >
    > I didn't make it clear in my example, but the column A in worksheet AAA
    > can be of any length. What I mean is: I don't know where the bottom of
    > that column is, and it can change (get longer) at any time. Instead of
    > AAA!A$1:A$9, I've tried AAA!A:A, but it doesn't seem to work. Or is
    > there another way to say "the whole column"? Remember there are blank
    > cells in that column, too, so I can't just go until it hits a blank. I'm
    > sure yours would work if the A and B columns were fixed length, but they
    > aren't in this case. How do I do a column of unknown length?
    >
    > Second: Does that require CTRL+SHIFT+ENTER?? Meaning: Is it an "array
    > formula"? I'm not very much "up" on those. Please let me know if that
    > is needed/required for this formula. (I've tried it, but it doesn't seem
    > to make any difference. I don't think it is needed, but...)
    >
    > Here's what I'm putting in right now (modified from your suggestion).
    > =IF(SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y"))=0,
    > "",SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y")))
    >
    > I get #NUM! and the step-by-step seems to indicate the first AAA!A:A
    > is the problem.
    >
    > Mike
    >
    >
    > On Sat, 1 Jan 2005 18:45:20 -0800, Biff <biffinpitt@comcast.net> wrote:
    >
    >> Hi!
    >>
    >> This formula will return the 0's:
    >>
    >> =SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    >>
    >> 1
    >> 0
    >> 2
    >> 0
    >> 0
    >> 0
    >> 1
    >>
    >>
    >> This formula will return ""'s in place of 0's:
    >>
    >> =IF(SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    >> =0,"",SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y")))
    >>
    >> 1
    >> blank
    >> 2
    >> blank
    >> blank
    >> blank
    >> 1
    >>
    >>
    >> Or, use the shorter formula and set to not display zero
    >> values. Tools>Options>View>Zero values.
    >>
    >> Copy whichever formula you choose down column D in CCC!.
    >>
    >> Biff
    >>
    >>> -----Original Message-----
    >>> Hi!
    >>> I hope the title uses the right terminology, but

    >> here's what I am
    >>> trying to do (with greatly simplified example).
    >>>
    >>> Single workbook. Excel 2002. I'm using two

    >> worksheets, but the
    >>> same could apply within a single worksheet.
    >>>
    >>> In worksheet CCC, column C contains the list of

    >> valid strings for
    >>> the AAA!A cells. Each string appears once and

    >> only once.
    >>>
    >>> On worksheet AAA, each cell in column A might

    >> contain any ONE of
    >>> several strings (MMM, OOO, PPP, etc) or it might

    >> be empty. There
    >>> may be several of each string in this column, and

    >> some strings may
    >>> not appear at all.
    >>>
    >>> On the same worksheet, cells in column B will

    >> contain either "Y",
    >>> "N", or be blank.
    >>>
    >>> I want to have worksheet CCC, column D contain the

    >> count of rows
    >>> in AAA where AAA!Ax matches CCC!Cx ---AND--- AAA!

    >> Bx == "Y".
    >>>
    >>> I have tried several functions and some

    >> combinations of functions,
    >>> but I can't get past the fact that both AAA!A:A

    >> and AAA!B:B might
    >>> contain blank cells. COUNT, COUNTA, DCOUNT, and

    >> DCOUNTA don't
    >>> seem to like the "database" on worksheet AAA.
    >>>
    >>> Example:
    >>> Worksheet AAA:
    >>> Column: A B
    >>> MMM (blank)
    >>> (blank) (blank)
    >>> OOO Y
    >>> MMM Y
    >>> PPP (blank)
    >>> SSS N
    >>> (blank) Y
    >>> OOO Y
    >>> SSS Y
    >>>
    >>> Worksheet CCC:
    >>> Column: C desired D (count of matches

    >> + "Y")
    >>> MMM 1
    >>> NNN 0 (or blank)
    >>> OOO 2
    >>> PPP 0 (or blank)
    >>> QQQ 0 (or blank)
    >>> RRR 0 (or blank)
    >>> SSS 1
    >>>
    >>> What kind of formula can I plunk into CCC!D:D to

    >> get the result
    >>> I'm looking for?? I'm pretty sure it won't be a

    >> simple one.
    >>>
    >>> Thanks for any help/pointers you can give.
    >>>
    >>> Mike
    >>> .
    >>>

    >>

    >
    >



  8. #8
    Dave Peterson
    Guest

    Re: How to count rows that match two criteria?

    Array formulas (including =sumproduct()) can't use the whole column.

    You did good to estimate how many rows you'll ever use and then go a little
    bigger.

    "Broida (spamless)" wrote:
    >
    > For a dirty workaround, I just used: AAA!A$1:A$10000
    > It's ugly, but as long as I don't hit 10,000 entries in
    > that column, it works just fine.
    >
    > If there is a good way to say "use the whole column"
    > (even when there are blanks in the column), please let
    > me know.
    >
    > Thanks!
    > Mike
    >
    > On Sun, 02 Jan 2005 12:53:54 -0600, Broida (spamless) <Broida@charter.not.net> wrote:
    >
    > > Thanks, Biff! I'm trying that right now and having a slight problem.
    > >
    > > Two additional questions.
    > >
    > > I didn't make it clear in my example, but the column A in worksheet AAA
    > > can be of any length. What I mean is: I don't know where the bottom of
    > > that column is, and it can change (get longer) at any time. Instead of
    > > AAA!A$1:A$9, I've tried AAA!A:A, but it doesn't seem to work. Or is
    > > there another way to say "the whole column"? Remember there are blank
    > > cells in that column, too, so I can't just go until it hits a blank. I'm
    > > sure yours would work if the A and B columns were fixed length, but they
    > > aren't in this case. How do I do a column of unknown length?
    > >
    > > Second: Does that require CTRL+SHIFT+ENTER?? Meaning: Is it an "array
    > > formula"? I'm not very much "up" on those. Please let me know if that
    > > is needed/required for this formula. (I've tried it, but it doesn't seem
    > > to make any difference. I don't think it is needed, but...)
    > >
    > > Here's what I'm putting in right now (modified from your suggestion).
    > > =IF(SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y"))=0,
    > > "",SUMPRODUCT(--(AAA!A:A=C1),--(AAA!B:B="Y")))
    > >
    > > I get #NUM! and the step-by-step seems to indicate the first AAA!A:A
    > > is the problem.
    > >
    > > Mike
    > >
    > >
    > > On Sat, 1 Jan 2005 18:45:20 -0800, Biff <biffinpitt@comcast.net> wrote:
    > >
    > >> Hi!
    > >>
    > >> This formula will return the 0's:
    > >>
    > >> =SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    > >>
    > >> 1
    > >> 0
    > >> 2
    > >> 0
    > >> 0
    > >> 0
    > >> 1
    > >>
    > >>
    > >> This formula will return ""'s in place of 0's:
    > >>
    > >> =IF(SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y"))
    > >> =0,"",SUMPRODUCT(--(AAA!A$1:A$9=C1),--(AAA!B$1:B$9="Y")))
    > >>
    > >> 1
    > >> blank
    > >> 2
    > >> blank
    > >> blank
    > >> blank
    > >> 1
    > >>
    > >>
    > >> Or, use the shorter formula and set to not display zero
    > >> values. Tools>Options>View>Zero values.
    > >>
    > >> Copy whichever formula you choose down column D in CCC!.
    > >>
    > >> Biff
    > >>
    > >>> -----Original Message-----
    > >>> Hi!
    > >>> I hope the title uses the right terminology, but
    > >> here's what I am
    > >>> trying to do (with greatly simplified example).
    > >>>
    > >>> Single workbook. Excel 2002. I'm using two
    > >> worksheets, but the
    > >>> same could apply within a single worksheet.
    > >>>
    > >>> In worksheet CCC, column C contains the list of
    > >> valid strings for
    > >>> the AAA!A cells. Each string appears once and
    > >> only once.
    > >>>
    > >>> On worksheet AAA, each cell in column A might
    > >> contain any ONE of
    > >>> several strings (MMM, OOO, PPP, etc) or it might
    > >> be empty. There
    > >>> may be several of each string in this column, and
    > >> some strings may
    > >>> not appear at all.
    > >>>
    > >>> On the same worksheet, cells in column B will
    > >> contain either "Y",
    > >>> "N", or be blank.
    > >>>
    > >>> I want to have worksheet CCC, column D contain the
    > >> count of rows
    > >>> in AAA where AAA!Ax matches CCC!Cx ---AND--- AAA!
    > >> Bx == "Y".
    > >>>
    > >>> I have tried several functions and some
    > >> combinations of functions,
    > >>> but I can't get past the fact that both AAA!A:A
    > >> and AAA!B:B might
    > >>> contain blank cells. COUNT, COUNTA, DCOUNT, and
    > >> DCOUNTA don't
    > >>> seem to like the "database" on worksheet AAA.
    > >>>
    > >>> Example:
    > >>> Worksheet AAA:
    > >>> Column: A B
    > >>> MMM (blank)
    > >>> (blank) (blank)
    > >>> OOO Y
    > >>> MMM Y
    > >>> PPP (blank)
    > >>> SSS N
    > >>> (blank) Y
    > >>> OOO Y
    > >>> SSS Y
    > >>>
    > >>> Worksheet CCC:
    > >>> Column: C desired D (count of matches
    > >> + "Y")
    > >>> MMM 1
    > >>> NNN 0 (or blank)
    > >>> OOO 2
    > >>> PPP 0 (or blank)
    > >>> QQQ 0 (or blank)
    > >>> RRR 0 (or blank)
    > >>> SSS 1
    > >>>
    > >>> What kind of formula can I plunk into CCC!D:D to
    > >> get the result
    > >>> I'm looking for?? I'm pretty sure it won't be a
    > >> simple one.
    > >>>
    > >>> Thanks for any help/pointers you can give.
    > >>>
    > >>> Mike
    > >>> .
    > >>>
    > >>

    > >
    > >


    --

    Dave Peterson

+ 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