# SUMPRODUCT of related arrays of different sizes

We are searching the Product of values from two arrays matching different criteria. The arrays are not the same size.

Example:

Array1
Date          Value
1.1.15         10
1.2.15         12
1.3.15         11

Array2
Date          Type          Value
1.1.15         a                 2
1.1.15         b                 3
1.2.15         a                 4
1.2.15         b                 5
1.3.15         a                 6
1.3.15         b                 7

Expected Results:
criteria1: date<=1.1.15 (array1 and array2)
criteria2: type a (array2)

formula: 10*2 = 20

criteria1: date<=1.2.15 (array1 and array2)
criteria2: type a (array2)

formula: 10*2 + 12*4 = 68

criteria1: date<=1.3.15 (array1 and array2)
criteria2: type a (array2)

formula: 10*2 + 12*4 +11*6 = 134

We know that we can get the result when we use an auxiliary column to store an interim result, but we want to get the result at once.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
It appears that you need to do a lookup on the first array to get an additional column in the second array. If you want to avoid adding this extra column to the second array, you can frequently use SUMIF in the SUMPRODUCT formula instead of a VLOOKUP.

The magic occurs by using an array (the range E2:E7) as the criteria in the SUMIF. When you use an array of criteria in a SUMIF, you get an array of results--one for each value in the criteria array (dates in E2:E7). If you put such a SUMIF inside a SUMPRODUCT or array-entered formula, that array can match up with other arrays of Boolean expressions. The main assumption with such an approach is that the dates in the criteria occur only once in the array being tested by the SUMIF (i.e. the first array).

The resulting formula gives the specified results for the three sample problems in your question. It is:
=SUMPRODUCT((SUMIF(A\$2:A\$4,E\$2:E\$7,B\$2:B\$4))*(E\$2:E\$7<=A10)*(F\$2:F\$7=B10)*(G\$2:G\$7))

In the above formula, the first array is in A2:B4 and the second is in E2:G7. The date criteria is in A10 and the type criteria is in B10. See attached workbook so you can test the formula and see how it works.
Sumproduct2arraysQ28737360.xlsx

Experts Exchange Solution brought to you by