Complicated sumproduct help reqd.

[Syed]

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: ? ? ? ? ?

 

[Govind]

Hi,

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

=SUMPRODUCT(($A$3:$A$8="Tower1")*($B$3:$B$8)*(C3:C8))*SUMPRODUCT(($A$14:$A$16="Tower1")*(C14:C16))
+SUMPRODUCT(($A$3:$A$8="Tower2")*($B$3:$B$8)*(C3:C8))*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.

 

[Biff]

No short and sweet way to do this (based on your layout)

Enter in C15 and copy across as needed:

=SUMPRODUCT(($A3:$A8=$A11)*($B3:$B8*C11)*C3:C8)+SUMPRODUCT(($A3:$A8=$A12)*($B3:$B8*C12)*C3:C8)+SUMPRODUCT(($A3:$A8=$A13)*($B3:$B8*C13)*C3:C8)

Biff

 

[sk]

Hi Syed,

Try the following -
={SUMPRODUCT(--(A3:A8={"Tower1","Tower2","Tower3"})*TRANSPOSE(C11:C13)*B3:B8*C3:C8)}

Use Ctr+Shift+Enter since this is an array based function

Cheers
sk

 

[Domenic]

Try...

C15, copied across:

=SUMPRODUCT($B$3:$B$8,C3:C8,SUMIF($A$11:$A$13,$A$3:$A$8,C11:C13))

Hope this helps!

 

[Biff]

Slight typo:

No short and sweet way to do this

Should read:

No short and sweet way to do this unless Domenic chimes in!

Biff

 

[Syed]

Thanks sk and Domenic! That's exactly what I needed.

Govind and Biff -- I originally thought of doing it the way you suggested --
but since I have 15 towers in different sheets, the formula becomes too big
to fit in a single cell.