View Single Post
  #4   Report Post  
Domenic
 
Posts: n/a
Default

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!