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

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(IF($A$1:$O$1=1, $A$2:$O$2),IF($A$1:$O$1=2, $A$3:$O$3))}

BTW this is crossposted here:

http://www.mrexcel.com/forum/excel-q...ml#post4212628

Does this help? By telling False to do "" then it is not counted in the formula for MIN value.

Please Login or Register  to view this content.

Sorry I misposted the formula originally. I am not using MIN. Below is my current formula.

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

I need the sumproduct of the arrays not the minimum values in the arrays.

In the example given above it would look like this:

1 X 1 = 1
2 X 2 = 4
3 X 3 = 9

Sum = 14

Really I am trying to create arrays of only the True values then perform a sumproduct of those arrays. Problem is the IF statement doesn't function that way. It just puts false in the array in place of the actual values that aren't true rather than ignoring them.

Can you post a spreadsheet of what you are trying to do? You can add it by going to "Go Advanced" and using the paperclip icon.

This will give you the first value of location 1 and the first value of location 2 in the file you attached:

Please Login or Register  to view this content.

This array formula gets the result you are lookng for, but it isn't pretty:

Please Login or Register  to view this content.

Originally Posted by WHER

See if the attachment gives you the result you were looking for:

Very clever. Works perfect. Thanks for the help!

For those that didn't download the file the code is below:

{=SUMPRODUCT(SMALL(IF(B3:K3="Volume",B4:K4,""),ROW(INDIRECT("1:"&COUNTIF(B3:K3,"Volume")))),SMALL(IF(L3:Q3="Price", L4:Q4,""),ROW(INDIRECT("1:"&COUNTIF(L3:Q3,"Price")))))}

Nigel,

Your code also works but not for a dynamic range. Would need to keep adding iterations of the code for each row in the arrays (aka additional "location"). Appreciate the effort though. I was investigating something similar as this is close to how I've created lists of unique values in other sheets.

Thanks all for the help!

Looks like I jumped on that solution too quickly. After messing with it some more I realized that it actually reorders the arrays from smallest to largest. Unfortunately this throws the data off quite a bit.

Instead of what should be

4 1
3 2
2 3

giving you a sum product of 16

You get

2 1
3 2
4 3

giving you a sum product of 20

Back to the drawing board.

I've attached an updated spreadsheet that shows what I am referring to.

This also works.

Please Login or Register  to view this content.

Does anyone know why the N(if(1 section is required for row to return an array to small and the results of small as an array back to index? If I take either of those out then row doesn't return an array back to small or small will not pass the full array back to index.

Side note. I did not know that Index could handle an array as the result of which column or row to look at. I will have to look at that more.

Originally Posted by nigelbloomy

This also works.

Please Login or Register  to view this content.

Does anyone know why the N(if(1 section is required for row to return an array to small and the results of small as an array back to index? If I take either of those out then row doesn't return an array back to small or small will not pass the full array back to index.

Side note. I did not know that Index could handle an array as the result of which column or row to look at. I will have to look at that more.

You might find this series of articles helpful. The construction you are using is among those discussed. You may have to do some searching from there.

http://excelxor.com/2014/11/04/coerc...las/#more-2591

Hope this helps.

Originally Posted by FlameRetired

You might find this series of articles helpful. The construction you are using is among those discussed. You may have to do some searching from there.

Excelxor has some great articles. Thank you finding this one for me.

@nigelbloomy

You're welcome. Glad it helps.