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$3 5: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.
--
David Biddulph

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


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.
--
Thank for your help
BeSmart