On Thu, 4 Oct 2007, in microsoft.public.excel.worksheet.functions,
Pete_UK said:

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.

In that case, you're really not going to like the solution I finally
came up with :-)

I finally realised I was never going to get an array formula to work
properly with Excel Boolean functions, and that I would have to fake
them. So I changed the binary format again, to 1, 0, and blank, and
where I had had the OR() function, I used:

=SIGN( (A$11:A$18=A21)+ISBLANK(A21) )

instead. The sum acts as an OR, and the SIGN() function keeps the sum
from becoming more than 1.

Then, to mimic an AND(), I multiplied three such expressions together,

=SIGN( (A$11:A$18=A21)+ISBLANK(A21) ) *
SIGN( (B$11:B$18=B21)+ISBLANK(B21) ) *
SIGN( (C$11:C$18=C21)+ISBLANK(C21) )

Then-- and here at last comes the array bit-- I used TRANSPOSE, and
MMULT to multiply the binaries and the data:

=MMULT(TRANSPOSE(D$11:D$18),
SIGN( (A$11:A$18=A21)+ISBLANK(A21) ) *
SIGN( (B$11:B$18=B21)+ISBLANK(B21) ) *
SIGN( (C$11:C$18=C21)+ISBLANK(C21) ) )

The final spreadsheet looks like this:

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

20: A B C Sum
21: {=MMULT(TRANSPOSE(D$11:D$18)
, SIGN( (A$11:A$18=A21)+ISBLANK(A21) )
* SIGN( (B$11:B$18=B21)+ISBLANK(B21) )
* SIGN( (C$11:C$18=C21)+ISBLANK(C21) ) )
22: 1 {=MMULT(TRANSPOSE(D$11:D$18),
23: 1 {=MMULT(TRANSPOSE(D$11:D$18),
24: 1 1 {=MMULT(TRANSPOSE(D$11:D$18),
25: 1 {=MMULT(TRANSPOSE(D$11:D$18),
26: 1 1 {=MMULT(TRANSPOSE(D$11:D$18),
27: 1 1 {=MMULT(TRANSPOSE(D$11:D$18),
28: 1 1 1 {=MMULT(TRANSPOSE(D$11:D$18),

(the long expressions, except for cell E21, are truncated to save space
here, obviously)

and the calculated figures look like this:

A B C Data
0 0 0 42
1 0 0 23
0 1 0 16
1 1 0 26
0 0 1 20
1 0 1 11
0 1 1 10
1 1 1 51

A B C Sum
199
1 111
1 103
1 1 77
1 92
1 1 62
1 1 61
1 1 1 51

A B C Sum
0 0 0 42
0 0 65
0 0 58
0 107
0 0 62
0 96
0 88
199

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

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

Thanks, and although it doesn't look as though I've used any of your
advice, I couldn't have got here without this discussion. I would
welcome any further advice on getting a more compact (but clear and
manageable) function than this one.

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