Sumproduct with Arrays of Different Sizes

I would like to use sumproduct with ranges of different sizes by padding the smaller array with zeros. Any suggestions?

For example, if the first array is always a fixed array with the following values:
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

and the second array can vary in length, with example values:
1, 2, 3

I would like a function that uses sumproduct to return the result: 116 (=0*10+0*11+0*12+...+1*18+2*19+3*20)

I think the real question here might be how to pad an array with a dynamic number of zeros...

Here is a still simplified but very realistic version of what I'm trying to do.

The attached excel spreadsheet has two input tables:
- One is a table of the expected percentage of people who are still alive at the end of age X, from 107 to 0 (descending order)
- The second is a table of the number of people born in each year, from 2010 to 2055

There is one output table:
- The total year-end living population of people born since 2010, for every year from 2010 to 2055

I have completed three of the blanks of the output table, but would like a methodology to complete the rest. I can only use excel functions, i.e. only non-VBA solutions.

Thanks in advance!

Solution attached.

I'm trying to do a similar thing, can you do this without putting the cumulative survival rate from the bottom up. My problem, specifically deals with oil and gas production by year and wells drilled in past years.

Very simplified, but this is essentially the problem:


Wells drilled
2000 5
2001 4
2002 3
2003 2
2004 1
2005 0

Each well produces:

10 bbl - y1
8 bbl - y2
6 bbl - y3
4 bbl - y4
2 bbl - y5

2002 for example, I want 3 wells producing 10 bbl, 4 wells producing 8 bbl, and 5 wells producing 6 bbl. I've got the sumproduct from the earlier implemented, but I get the result of 3 wells producing 10 bbl, but 2 wells producing 8 bbl, (2003 instead of 2001) and 1 well producing 6 bbl. Both columns and height are set as 3, but instead of going up to my reference (2000) my offset is going down, is there any way to correct this without flipping my data?

That's a great question, as I initially dealt with the same issue. In my data set, I had a column with ascending data like what you showed. I needed to (a) transpose the data to put it into a row and (b) reverse the data so it was in descending order. I used the following array formula:

{=TRANSPOSE(N(OFFSET($H$28:$H$63,ROWS($C$28:$C$63)-(ROW($C$28:$C$63)-ROW($C$28)+1),0,1,1)))}

Column H had my equivalent to your 'bbl' data in rows 28-63; column C had my 'years' data

Once I had created this new row, I used the method from my previous post. It should be possible, albeit cumbersome, to combine these two operations into a single calculation.

Oh cool, I wouldn't have thought of using a negative height/width. Thank you!

Hi, Did you ever solve this problem?

Thanks
Ezzy

Hi! I'm new to the forum and am having a problem when trying to multiply arrays of differing sizes too. I will post a file showing the problem.

But... basically, I have a file with several line items (as many as a few hundred) that each have individual weights (hours of effort estimated for each). Each of these line items follow the same process steps from start to finish (columns), with each of these steps weighted for earnings. Each line item has a date in the intersection between the line and the column containing the process step. What I need to do: for all line items that have a date <= a status date in each column, sumproduct (earnings weight [at top of columns] * weighted effort [hours on each line]) then divide that value by the total effort (sum of the effort on all lines).

I used brute force in the past (starting on line 20 and following, columns C, D, and E ...

=(($I$44*(SUMIF($I$46:$I$314,"<="&$B325,$G$46:$G$314)))+($J$44*(SUMIF($J$46:$J$314,"<="&$B325,$G$46:$G$314)))+($K$44*(SUMIF($K$46:$K$314,"<="&$B325,$G$46:$G$314)))+($L$44*(SUMIF($L$46:$L$314,"<="&$B325,$G$46:$G$314)))+($M$44*(SUMIF($M$46:$M$314,"<="&$B325,$G$46:$G$314)))+($N$44*(SUMIF($N$46:$N$314,"<="&$B325,$G$46:$G$314)))+($O$44*(SUMIF($O$46:$O$314,"<="&$B325,$G$46:$G$314))))/$G$315

but would very much like to find out how to perform these calculations in a more elegant way. MMULT doesn't work because I may have 300 line items and 10 columns, which violates the MMULT array rules. It also constrains how many process steps (columns) can be used since I have to manually adjust each formula to accommodate additional columns... would love to be able to insert additional columns as needed.