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

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

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)+SU MPRODUCT(($A3:$A8=$A12)*($B3:$B8*C12)*C3:C8)+SUMPR ODUCT(($A3:$A8=$A13)*($B3:$B8*C13)*C3:C8)

Biff

"Syed" wrote in message
...
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: ? ? ? ? ?

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

Biff wrote:
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)+SU MPRODUCT(($A3:$A8=$A12)*($B3:$B8*C12)*C3:C8)+SUMPR ODUCT(($A3:$A8=$A13)*($B3:$B8*C13)*C3:C8)

Biff

"Syed" wrote in message
...
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: ? ? ? ? ?

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!

In article ,
"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: ? ? ? ? ?

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

"Domenic" wrote in message
...
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!

In article ,
"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: ? ? ? ? ?

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.


"Domenic" wrote:

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!

In article ,
"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: ? ? ? ? ?