CODEWITHSUNDEEP
excel
sidewaysglance
sidewaysglance

Reputation: 177

SUMPRODUCT INDEX MATCH

With the following data:

    A       B
1   CUMULATIVE PERCENTAGE OF ITEMS PRODUCED PER MONTH ("COMPLETION_TABLE")
2   Type     Month 1   Month 2
3   KITTENS  0         10
4   FISH     0         20
5   BANANAS  2         5
6   APPLES   0         0
7   PEARS    0         5
8   KITTENS  0         5
9           
10          
11  PRICES TABLE ("PRICES_TABLE")
12  Type     Value  
13  APPLES   1000   
14  BANANAS  5000   
15  PEARS    3000   
16  FISH     4000   
17  KITTENS  2000

I'm attempting to use the SUMPRODUCT function to calculate the percentage change in each month and use that value as a multiple of the prices table to provide a total price per month across all types that have been produced.

I can calculate the movement as:

=SUMPRODUCT((COMPLETION_TABLE[Month 2]-COMPLETION_TABLE[Month 1]))

... but I then need to calculate the portion of the individual movement values against the price for that type and sum the resulting products together. I have been using various INDEX / MATCH combinations without much luck.

As an example: BANANAS which should =(5-2)*5000.

Written as expanded arrays I would like to do 

({10;20;5;0;5;5}-{0;0;2;0;0;0})*{2000;4000;5000;1000;3000;2000}.

Upvotes: 2

Views: 7804

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

From your question I understand the result you want is an array. This is what you get with this formula:

=INDEX($B$13:$B$17,MATCH($A3:$A8,$A$13:$A$17,0))*($C3:$C8-$B3:$B8)

entered as an array formula using Ctrl Shift Enter.

enter image description here

Upvotes: 3

barry houdini
barry houdini

Reputation: 46341

Use of SUMPRODUCT implies you want a single figure result. You can use SUMIF as a "pseudo lookup" within SUMPRODUCT to get the prices, e.g.

=SUMPRODUCT(C3:C8-B3:B8,SUMIF(A13:A17,A3:A8,B13:B17))

That would get you a result of 140,000 for your example

Upvotes: 3

dimitris_ps
dimitris_ps

Reputation: 5951

I am sure there is something simpler. I am assuming that the Type in the Price Table are unique:

{=(SUM((A13=$A$3:$A$8)*$C$3:$C$8)-SUM((A13=$A$3:$A$8)*$B$3:$B$8))*SUM((A13=$A$13:$A$17)*$B$13:$B$17)

Upvotes: 2

Related Questions