Sumproduct formula

[BeSmart]

Hi All

Is there a smarter way of doing this SUMPRODUCT formula?

I'm finding different duration totals and multiplying the total by a
different ratio for each duration
e.g.
find the 30 durations and multiple by the 30 ratio of 0.5 (cell name =
'thirty'),
find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five')
etc

All named ranges are the same size i.e. cells 35:76

$D217 = the market to search for in
the named range "market" = range (A35:A76)

BO198 = the duration to search for in
the named range "duration" = range(B35:B76)

All parts are the same except for:
- the "duration =$BO$198" section which needs to move one column right each
time
- the named ranges must change (in the order as per the current formula)
"thirty" or "five" or "ten" etc

I also need to be able to copy the formula across 52 columns and down 10 rows.


=SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198),I$35:I$76)*thirty
+SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35:I$76)*five
+SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35:I$76)*ten
+SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35:I$76)*fifteen
+SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35:I$76)*twenty
+SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35:I$76)*fortyfive
+SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35:I$76)*Sixty
+SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35:I$76)*ninety
+SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35:I$76)*onetwenty)*10))*$G217

Any advice would be greatly appreciated.

 

[BeSmart]

In addition, the sumproduct formula causes a circular reference because
within the range of rows 35:76 , row 52 is different - it total the first
group of markets.
(It's formula = the sum of the first 5 sumproduct formula and therefore
causes a circular reference).

How can I exclude row 52 from the sumproduct formula to stop the circular
reference?

 

[David Biddulph]

To start with, it looks as if your SUM function isn't doing anything.

=SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198),I$35:I$76)*thirty
+SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35:I$76)*five
+SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35:I$76)*ten
+SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35:I$76)*fifteen
+SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35:I$76)*twenty
+SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35:I$76)*fortyfive
+SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35:I$76)*Sixty
+SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35:I$76)*ninety
+SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35:I$76)*onetwenty)*10))*$G217

ought to be able to be changed to

=(SUMPRODUCT((market=$D217)*(duration=$BO$198),I$35:I$76)*thirty
+SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35:I$76)*five
+SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35:I$76)*ten
+SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35:I$76)*fifteen
+SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35:I$76)*twenty
+SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35:I$76)*fortyfive
+SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35:I$76)*Sixty
+SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35:I$76)*ninety
+SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35:I$76)*onetwenty)*10*$G217

To get rid of your circular reference the easy option to try would seem to
be to cut out row 52 and paste it somewhere outside the range.

 

[Teethless mama]

your formula can shorten to this:

=SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$198)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*$G217

 

[Teethless mama]

correction:

=SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$198)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*10*$G217

 

[BeSmart]

Thanks Teethless Mama!!!

That worked wonderfully - and I can understand exactly what the formula is
doing.
I've definitely learnt a new and smarter way of doing the formula and I'll
use it heaps!!!

One question: The formula works if I enter numbers between the {...}, but
it doesn't seem to like named ranges. Is there a way I can use named ranges
(that report a cell on sheet2) in this area?

i.e. instead of typing "{30,5,10...}" into the formula how do I enter the
named range "{thirty,five,ten...}" which read cells on sheet2 in cell K3, K4,
K5...?

 

[Lars-Åke Aspelin]

Thanks Teethless Mama!!!

That worked wonderfully - and I can understand exactly what the formula is
doing.
I've definitely learnt a new and smarter way of doing the formula and I'll
use it heaps!!!

One question: The formula works if I enter numbers between the {...}, but
it doesn't seem to like named ranges. Is there a way I can use named ranges
(that report a cell on sheet2) in this area?

i.e. instead of typing "{30,5,10...}" into the formula how do I enter the
named range "{thirty,five,ten...}" which read cells on sheet2 in cell K3, K4,
K5...?

If the parameters (30,5,10,...120) are all in an contigous range, i.e
K3:K11 you can replace the vector {3,5,10...} in the formula with
TRANSPOSE(K3:K11).

You also have to confirm the formula with CTRL+SHIFT+ENTER rather than
just ENTER.

The range K3:K11 can be named if you want, like my_factors or
something that describes what it contains.

TRANSPOSE is needed to make a row vector out of the column vector
K3:K11 to fit the other row vectors in the formula.

Hope this helps / Lars-Åke

 

[BeSmart]

Thanks Lars-Ã…ke

That works perfectly and I now know how to incorporate rows of data into
formulas by using Transpose within the formula.

I really appreciate your help!!!
You're SMART!!!
BeSmart