In article ,
"Syed" wrote:
Based on that formula, I
believe the following will work for the remaining calculations:
For K39 (copied across):
=SUM(MMULT((($D$6:$D$17=$D39)*($E$6:$E$17=$E$39))* ($E$6:$E$17=TRANSPOSE($E$21:
$E$23)),$Q$21:$AB$23)*($Q$6:$AB$17)*($F$6:$F$17)*( $Q$2:$AB$2=K$2))
One very minor detail, you can get rid of one set of brackets...
=SUM(MMULT(($D$6:$D$17=$D39)*($E$6:$E$17=$E$39)*($ E$6:$E$17=
TRANSPOSE($E$21:$E$23)),$Q$21:$AB$23)*($Q$6:$AB$17 )*($F$6:$F$17)*
($Q$2:$AB$2=K$2))
For Q39 (copied across):
=SUM(MMULT((($D$6:$D$17=$D39)*($E$6:$E$17=$E$39))* ($E$6:$E$17=TRANSPOSE($E$21:
$E$23)),$Q$21:$AB$23)*($Q$6:$AB$17)*($F$6:$F$17)*( $Q$1:$AB$1=Q$1))
Can be shortened to...
=SUM(MMULT(($D$6:$D$17=$D39)*($E$6:$E$17=$E39)*($E $6:$E$17=
TRANSPOSE($E$21:$E$23)),Q$21:Q$23)*(Q$6:Q$17)*($F$ 6:$F$17))
Alternatively, you can use...
=SUMPRODUCT(--($D$6:$D$17=$D39),--($E$6:$E$17=
$E39),SUMIF($E$21:$E$23,$E$6:$E$17,Q$21:Q$23),$F$6 :$F$17,Q$6:Q$17)
....confirmed with just ENTER.
Hope this helps!
|