How to count rows that match two criteria?

G

Guest

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
 
B

Biff

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
 
G

Guest

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
 
G

Guest

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
 
D

Dave Peterson

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