Hi ,
If you can indicate the complete addition that you wish to make , on the lines of :
Sheet1!$D$3:$D$7000=$A$4
it will be easier to suggest what should be the revised formula.
If you can work it out for yourself , do it this way :
The outer SUMPRODUCT is merely taking those values from those rows which satisfy the criterion :
Sheet1!$D$3:$D$7000=$A$4
from those columns which satisfy the criterion :
Sheet1!$C$1:$AR$1=C2
Since SUMPRODUCT anyway multiplies each and every element of two arrays when one is a column vector and the other is a row vector , the MMULT is unnecessary in this case.
MMULT of two matrices where one matrix A is m x n dimensioned ( m rows and n columns ) and another matrix B is n x o dimensioned ( n rows and o columns ) will result in an output matrix of dimensions m x o ( m rows and o columns ).
In your case , A is a single column vector having 6998 rows ; B is a single row vector having 42 columns ; the result of the MMULT will therefore be :
6998 x 1 vector multiplied by 1 x 42 vector , which will give 6998 x 42 output matrix.
Where ever this matrix has a 1 , the corresponding value will be summed up by the SUMPRODUCT , since the range being summed up is also a 6998 x 42 matrix ( Sheet1!$C$3:$AR$7000 ).
Now , if you eliminate the MMULT , introducing the third criterion is simple ; use the following formula as a template :
=SUMPRODUCT((Sheet1!$D$3:$D$7=$A$4)*(Sheet1!$Y$3:$Y$7=$H$2)*(Sheet1!$C$1:$AR$1=C2),Sheet1!$C$3:$AR$7)
where the highlighted part is the third criterion.
Narayan
