Hi,

This might not be an efficient formula but it works. Try

=SUMPRODUCT(($A$3:$A$8="Tower1")*($B$3:$B$8)*(C3:C 8))*SUMPRODUCT(($A$14:$A$16="Tower1")*(C14:C16))
+SUMPRODUCT(($A$3:$A$8="Tower2")*($B$3:$B$8)*(C3:C 8))*SUMPRODUCT(($A$14:$A$16="Tower2")*(C14:C16))+
SUMPRODUCT(($A$3:$A$8="Tower3")*($B$3:$B$8)*(C3:C8 ))*SUMPRODUCT(($A$14:$A$16="Tower3")*(C14:C16))

A3 to A8 have your Tower names, B3 to B8 has the $/unit and C3 to C8 is
the months data in units. A14 to A16 has the tower names for the
escalation matrix and C14 to C16 has the respective months escalation
factor.

This formula is dynamic so you can copy it over to other months and it
will work.

Regards

Govind.


Syed wrote:

I have a somewhat complicated sumproduct I'm trying to calculate. First, I
have a table that contains number of units by month, and a cost per unit for
each line of entry (A3:G8 below). So one task is to multiply the number of
units x the cost per unit by each Tower for each month -- this is a simple
sumproduct formula.

But in addition to this multiplication, I also have to multiply each of the
Tower entries (units x cost) by an (escalation) factor (in A11:G13 below).

So for instance, in Month 1 (column C), I need to calculate the sumproduct
of all the number of units x the $ per unit x the escalation factor relevant
to each of the towers. Note that I need to do this by month, not for the
whole table at once. But I do need one formula to for each month.

Any suggestions? Thanks a lot in advance.

A B C D E F G
1 $ <----- Months -----
2 /unit Number of units
3 Tower1 100 1 1 1 2 3
4 Tower2 120 5 3 3 3 2
5 Tower2 105 3 4 7 10 10
6 Tower1 130 2 2 5 6 8
7 Tower3 100 8 10 12 15 15
8 Tower3 110 6 6 5 4 3
9
10 Escalation factors
11 Tower1 1.1 1.2 1.3 1.4 1.5
12 Tower2 1.0 1.0 1.0 1.0 1.0
13 Tower3 1.2 1.2 1.2 1.3 1.3
14
15 Total: ? ? ? ? ?