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
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
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
to get the resultI'm looking for?? I'm pretty sure it won't be a simple one.
Thanks for any help/pointers you can give.
Mike