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

Well, you've certainly taken it a bit further, Del !! (Not sure about
elegance, though <bg)

It's readable, maintainable and scalable, and it's got no helper cells
and no VBA. It'll do for my tastes. Latest improvement is removing the
asterisks in the SUMPRODUCT and replacing them with commas:

=SUMPRODUCT(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 only thing I think I could do to improve it at this point is if I
could work out how to turn those three separate factors into one array
expression. Then, adding a fourth, fifth, etc. would be as trivial as
changing a range reference. It's a rotten shame that proper Boolean
functions break when you try to use them in Excel array functions, so
that you have to fake it with silly sums.

I can't help wondering where the intial data comes from and if there
is some relationship between A B and C, along the lines of:

= constant + a*A + b*B + c*C

It's just a Venn diagram of three intersecting circles: you're given the
eight combinations of A only; A and B but not C; A, B and C etc. and the
challenge is how you answer questions like "How many in circle A
total?", "How many in neither A nor B?" and so on. "total in A" is
A+A&BnotC+A&CnotB+AB&C; "neither in A nor B" is CnotBorA+none, etc. The
key was some sort of matrix but I always had trouble with matrices at
school, and I also have trouble with arrays in Excel, so this was a
struggle to work through.

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