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]

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

 

[Aladin Akyurek]

Try to post a small sample along with the desired results.

 

[drew.j.harrison]

Can't seem to find a way to attach a sample file but I did upload one at the crosslinked thread. It is located here:

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

The array configuration is a bit different than what I posted above but more accurately represents what I am looking to do. Thanks again for the help.

 

[drew.j.harrison]

Solved on the cross posted thread. Thanks for the help guys! Below is the code.

{=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")))))}

 

[drew.j.harrison]

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 in the cross posted thread.

 

[Aladin Akyurek]

Can't seem to find a way to attach a sample file but I did upload one at the crosslinked thread. It is located here:

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

The array configuration is a bit different than what I posted above but more accurately represents what I am looking to do. Thanks again for the help.

The data representation which is adapted here is flawed...

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

2​

Location 1​

​

​

​

​

Location 2​

​

​

​

​

Location 1​

​

​

Location 2​

​

​

3​

Volume​

Other​

Other​

Other​

Other​

Volume​

Other​

Other​

Other​

Other​

Other​

Other​

Price​

Other​

Other​

Price​

4​

Product​

100​

42​

42​

42​

42​

200​

42​

42​

42​

42​

42​

42​

$ 1.00​

42​

42​

$ 2.00​

where you seem to want to associate:

Volume of 100 with Price of 1 and Volume of 200 with Price of 2

Imputing some logic into the foregoing something like: First volume occurrence X First price occurrence, what is the correlation when we have the following?

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

2​

Location 1​

​

​

​

​

Location 2​

​

​

​

​

Location 1​

​

​

Location 2​

​

​

3​

Volume​

VOLUME​

Other​

Other​

Other​

Volume​

Other​

Other​

Other​

Other​

Other​

Other​

Price​

Other​

Other​

Price​

4​

Product​

100​

42​

42​

42​

42​

200​

42​

42​

42​

42​

42​

42​

$ 1.00​

42​

42​

$ 2.00​

 

[drew.j.harrison]

The data representation which is adapted here is flawed...

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

2​

Location 1​

Location 2​

Location 1​

Location 2​

3​

Volume​

Other​

Other​

Other​

Other​

Volume​

Other​

Other​

Other​

Other​

Other​

Other​

Price​

Other​

Other​

Price​

4​

Product​

100​

42​

42​

42​

42​

200​

42​

42​

42​

42​

42​

42​

$ 1.00​

42​

42​

$ 2.00​

<tbody>
</tbody>

where you seem to want to associate:

Volume of 100 with Price of 1 and Volume of 200 with Price of 2

Imputing some logic into the foregoing something like: First volume occurrence X First price occurrence, what is the correlation when we have the following?

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

2​

Location 1​

Location 2​

Location 1​

Location 2​

3​

Volume​

VOLUME​

Other​

Other​

Other​

Volume​

Other​

Other​

Other​

Other​

Other​

Other​

Price​

Other​

Other​

Price​

4​

Product​

100​

42​

42​

42​

42​

200​

42​

42​

42​

42​

42​

42​

$ 1.00​

42​

42​

$ 2.00​

<tbody>
</tbody>

I don't understand your question. This is what the current sample data looks like:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

LOCATION 1

LOCATION 2

LOCATION 1

LOCATION 2

2

Volume

Other

Other

Other

Other

Volume

Other

Other

Other

Other

Other

Other

Price

Other

Other

Price

Total Spent

3

Product

200

42

42

42

42

50

42

42

42

42

42

42

$1.00

42

42

$2.00

Formula

<tbody>
</tbody>

​

The "Total Spent" should be the sumproduct of the following Arrays

B3 (200) X N3 ($1.00)
G3 (050) X Q3 ($2.00)

The value returned should be $300.00

If I use the following formula is get an error as it returns the following arrays with "False" values

{=SUMPRODUCT(IF(B3:K3="Volume",B4:K4),IF(L3:Q3="Price", L4:Q4))}

B3 (200) X___FALSE
_FALSE__X___FALSE
_FALSE__X_N3 ($1.00)
_FALSE__X___FALSE
_FALSE__X___FALSE
G3 (050) X Q3 ($2.00)
_FALSE__
_FALSE__
_FALSE__
_FALSE__

If I use the following formula is get an error as it reorders the arrays from lowest to highest value the following arrays

{=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")))))}

G3 (050) X N3 ($1.00)
B3 (200) X Q3 ($2.00)

This returns $450.00

The following works but requires addition iterations for each added location to the ranges. I need to develop a formula that can handle dynamic ranges so as columns are added for more locations it automatically adjusts.

{=SUM(INDEX(A4:Q4,1,LARGE((A3:Q3="Volume")*COLUMN(A3:Q3),1))*INDEX(A4:Q4,1,LARGE((A3:Q3="Price")*COLUMN(A3:Q3),1)),INDEX(A4:Q4,1,LARGE((A3:Q3="Volume")*COLUMN(A3:Q3),2))*INDEX(A4:Q4,1,LARGE((A3:Q3="Price")*COLUMN(A3:Q3),2)))}

I hope that clears up any confusion as to what I am looking to accomplish. Thanks.

 

[RoryA]

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.

 

[drew.j.harrison]

FYI the row numbers in the table I posted are incorrect. The rows should start at 2 not 1. Below is the corrected table:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

2

LOCATION 1

LOCATION 2

LOCATION 1

LOCATION 2

3

Volume

Other

Other

Other

Other

Volume

Other

Other

Other

Other

Other

Other

Price

Other

Other

Price

Total Spent

4

Product

200

42

42

42

42

50

42

42

42

42

42

42

$1.00

42

42

$2.00

Formula

<tbody>
</tbody>