More sumproduct/table multiplication help

[Syed]

Thanks to those who replied to my post yesterday about multiplying several
tables based on certain criteria. However, I’m still stumped in a few other
areas. Since it is somewhat of a complicated problem, I had to create a
spreadsheet to show it:
http://www.syedfaisal.com/TableMultProb.xls

I have a table containing number of units (rows 6:17) – each entry is
“tagged†with a Tower, a Source, and a cost per unit. I also have a table of
escalation factors for each Source (rows 21:23).

What I need to do is multiply the (number of units) x (cost per unit) x
(escalation factor appropriate to each Source), using ONLY the monthly units
and monthly escalation factors (blue cells only).

I’ve tried to do the calculations for the various breakdowns I’ll need on
rows 27:39 (the formula you posted is in Q27:AB29). However, I am unable to
figure out the formula for the cells in red. How can I calculate the red
cells using the data in the blue cells only?

Any thoughts on the formulae I'll need in cells K27, K39, and Q39?

Thanks in advance.

 

[Domenic]

Try...

K27, copied down and across:

=SUM(MMULT(($D$6:$D$17=$D27)*($E$6:$E$17=TRANSPOSE($E$21:$E$23)),$Q$21:$A
B$23)*($Q$6:$AB$17)*($F$6:$F$17)*($Q$2:$AB$2=K$2))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Syed]

Domenic -- that is just awesome! You saved my day. 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))

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

....both entered as array formulas (CTRL+SHIFT+ENTER).

 

[Domenic]

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!