# How to count rows that match two criteria?

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

4. ## 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. ## Re: How to count rows that match two criteria?

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

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. sumproduct requires a define row range such as a1:a1000

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

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