Sum of values that are each dependent on another cell

Question

I am currently working in Excel. I have a list of values in column B that are dependent on the values in column A, as well as one other cell - J8

413505  =B4/$J$8
413505  =B5/$J$8
420171  =B6/$J$8
544643  =B7/$J$8
604280  =B8/$J$8
621849  =B9/$J$8

For example if J8=2.5 I get

 413,505     165,269.79 
 413,505     165,269.79 
 420,172     167,934.33 
 544,644     217,683.33 
 604,281     241,519.03 
 621,849     248,540.88 

And SUM(B:B)=1206217.15. Let's call this value X

I want to calculate the value of X depending on loads of different values of J8. In fact, I have a 10x10 matrix of different values for J8 that I want to reference and then calculate the value of X in the same size matrix for each different value. E.g. the possible values of J8 could be:

1.753   1.594   1.461   1.349   1.253   1.169   1.096   1.032   0.975   0.924
1.650   1.500   1.375   1.270   1.179   1.101   1.032   0.972   0.918   0.870
1.559   1.417   1.299   1.200   1.114   1.040   0.975   0.918   0.867   0.822
1.477   1.343   1.231   1.137   1.056   0.986   0.924   0.870   0.822   0.779
1.404   1.276   1.170   1.080   1.004   0.937   0.878   0.827   0.781   0.740
1.337   1.216   1.115   1.029   0.956   0.893   0.837   0.788   0.744   0.705
1.277   1.161   1.064   0.983   0.913   0.852   0.799   0.752   0.711   0.674
1.221   1.111   1.018   0.940   0.874   0.816   0.765   0.720   0.680   0.645
1.171   1.065   0.976   0.902   0.837   0.782   0.733   0.690   0.652   0.618
1.124   1.022   0.938   0.866   0.804   0.751   0.704   0.663   0.626   0.594

I'm assuming I need some sort of array formula, but I'm having difficulties.

Answer 1

Use SUMPRODUCT():

=SUMPRODUCT($B$1:$B$6/D1)

This assumes D10 as your first cell in the 10 x 10.

Put in your first cell then copy over 10 and down 10.