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