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.


Date          Value
1.1.15         10
1.2.15         12
1.3.15         11

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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

byundtMechanical 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:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.