Well, it's a different approach. Seems more complex than my solution
to you, though, and as I pointed out you could combine the helper
columns into one composite formula if you wish.

One thing - I don't think you need the double unary minus in:

=D11*--AND(OR(ISBLANK(A$2),A$...

as the multiplication should coerce the TRUE/FALSE to 1 or 0.

Pete

On Oct 4, 8:43 am, Del Cotter wrote:
On Wed, 3 Oct 2007, in microsoft.public.excel.worksheet.functions,
Pete_UK said:

It is confusing that in your second table you are using N to indicate
"don't care" - I have assumed that you would leave it blank for this
and use N and Y for exact matches. I put your table including headings
in A1:E9, and used 1's and 0s instead of "Y" and "N". Then, allowing
space for you to make it 4 variables, I put C, B and A as headings in
A19:C19.

Your point about blanks jogged me to change how I present the problem,
and inspired me to make the following function the judge of whether to
count a row:

=OR(ISBLANK(A$2),A$2=A11)

This returns TRUE if cell A2 matches A11, or if A2 is blank whatever the
value of A11, which is what I wanted. Three of those together gives me:

=AND(OR(ISBLANK(A$2),A$2=A11),
OR(ISBLANK(B$2),B$2=B11),
OR(ISBLANK(C$2),C$2=C11))

And I can multiply that by the data in that row via a helper column like
so:

1: A B C Sum
2: TRUE TRUE =SUM(E11:E18)

10: A B C Data Helper
11: FALSE FALSE FALSE 42 =D11*--AND(OR(ISBLANK(A$2),A$...
12: TRUE FALSE FALSE 23 =D12*--AND(OR(ISBLANK(A$2),A$...
13: FALSE TRUE FALSE 16 =D13*--AND(OR(ISBLANK(A$2),A$...
14: TRUE TRUE FALSE 26 =D14*--AND(OR(ISBLANK(A$2),A$...
15: FALSE FALSE TRUE 20 =D15*--AND(OR(ISBLANK(A$2),A$...
16: TRUE FALSE TRUE 11 =D16*--AND(OR(ISBLANK(A$2),A$...
17: FALSE TRUE TRUE 10 =D17*--AND(OR(ISBLANK(A$2),A$...
18: TRUE TRUE TRUE 51 =D18*--AND(OR(ISBLANK(A$2),A$...

Resulting in the following:

1: A B C Sum
2: TRUE TRUE 77

10: A B C Data Helper
11: FALSE FALSE FALSE 42 0
12: TRUE FALSE FALSE 23 0
13: FALSE TRUE FALSE 16 0
14: TRUE TRUE FALSE 26 26
15: FALSE FALSE TRUE 20 0
16: TRUE FALSE TRUE 11 0
17: FALSE TRUE TRUE 10 0
18: TRUE TRUE TRUE 51 51

This gives me the result for *one* row, yay! Now why can't I make an
array function that cycles through eight rows (and does not need eight
helper columns) to give me the following?

10: A B C Data
11: FALSE FALSE FALSE 42
12: TRUE FALSE FALSE 23
13: FALSE TRUE FALSE 16
14: TRUE TRUE FALSE 26
15: FALSE FALSE TRUE 20
16: TRUE FALSE TRUE 11
17: FALSE TRUE TRUE 10
18: TRUE TRUE TRUE 51

20: A B C Sum
21: 199
22: TRUE 111
23: TRUE 103
24: TRUE TRUE 77
25: TRUE 92
26: TRUE TRUE 62
27: TRUE TRUE 61
28: TRUE TRUE TRUE 51

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.