Create an array from "True" only then perform Sumproduct???

[drew.j.harrison]

So I have two dynamic arrays that I am trying to get the Sumproduct of the 1st actual value in the arrays, then the 2nd, etc. I am having an issue cause the IF statement I am using to generate the arrays leaves "False" values in the arrays rather than just starting and continuing building the array on "True" Values. Below is an example of what is happening vs. what I need to happen:

False----1
--1----False
False--False
False----2
False--False
--2----False
False----3
False--False
--3----False

A Sumproduct of these 2 arrays returns zero as it is trying to multiply the actual values by their "False" counterpart.

Essentially I need the arrays to not include the "Falses" so they would end up looking like this.

1 1
2 2
3 3

Below is a sample of my current code. Any idea how I can go about getting the array to only return the true values and not Falses? Appreciate the help.

{=SUMPRODUCT(MIN(IF($A$1:$O$1=1, $A$2:$O$2)),MIN(IF($A$1:$O$1=2, $A$3:$O$3)))}

BTW this is crossposted here:

http://www.excelforum.com/excel-pro...only-then-perform-sumproduct.html#post4129593

 

[drew.j.harrison]

On the assumption you always have a 1 to 1 correlation of Volume and Price per location, based on your example workbook, I think you could use:

=SUMPRODUCT(INDEX(B4:Q4,1,N(IF(1,SMALL(IF($B$3:$Q$3="Volume",COLUMN($B$3:$Q$3)-MIN(COLUMN($B$3:$Q$3))+1),ROW(INDIRECT("1:"&COUNTIF($B$3:$Q$3,"Volume"))))))),INDEX(B4:Q4,1,N(IF(1,SMALL(IF($B$3:$Q$3="Price",COLUMN($B$3:$Q$3)-MIN(COLUMN($B$3:$Q$3))+1),ROW(INDIRECT("1:"&COUNTIF($B$3:$Q$3,"Price"))))))))

array-entered.

There is always a 1 to 1 correlation. Just evaluated the formula and it works great! Thanks again for the help! You guys are life savers.