Hi Domenic,

Thank you very much for assistance and a brilliant Formula. The Formula is
returning the expected results.

Is it possible to tweak the Formula based on my matrix type layout below.
The results are being returned to a chart / matrix layout: I have the
criterion vertically and horizontally and then they are referenced using the
horizontal and vertical cell address that houses the criterion, and the
result is returned to the intercept of the vertical and horizontal criterion.
So at some point both criterion values being referenced will be the same, if
possible can the Formula return a zero when this occurs?

Example:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and C1 criterion 81and 81


Criteria B1 houses 80, C1 houses 81, D1 houses 82, E1
houses 83, etc
A2 houses 80 0 4
9 7
A3 houses 81 5 0
3 2
A4 houses 82 7 3
0 1
A5 houses 83 2 7
5 0


So, I would use your Formula with the corresponding vertical and horizontal
cell references (and copy across and down):
=SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*MMULT(--
(Results=B$1),TRANSPOSE(COLUMN(Results)^0))0))
....confirmed with CONTROL+SHIFT+ENTER.

Cheers,
Sam

Domenic wrote:
Assuming that A1 contains your first criterion, such as 80, and B1
contains your second criterion, such as 87, try...

=SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul
ts=B1),TRANSPOSE(COLUMN(Results)^0))0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi All,

[quoted text clipped - 9 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1