summed product of multiple arrays
 

I have a spreadsheet that in simplified form looke like this:
A B C D E F G H I J K L M N
21 0.3 34000 101 102 103 106 209 304
21 0.5 40000 105 107 203 206 3
32 0.99 34000 11 10 198 99 909 904
32 0.01 36000 141 142 173 9006 809 804 1105 1204
33 0.03 34000 501 502 503 108
33 0.5 47000
42 0.6 34000
42 0.4 70100

In column F I need to make a sum of the values in Column C whe
where columnD =3400 and column D<40000 and column A is equal to any of the
value in GX:NX where X is the row number if column F

The only way I can see to do this is pretty long and messy:
{=SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=g2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=h2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=i2),(C1:C8))+
....
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=n2),(C1:C8))}

Is there a way to simplify this?

thanks.

summed product of multiple arrays
 

I should add in here that the columns are misaligned din my post. For the
first row,
A1=21
B1=""
C1=0.3
D1=3400
E1=""
F1=long formula
G1=101
etc

"koneil" wrote:

I have a spreadsheet that in simplified form looke like this:
A B C D E F G H I J K L M N
21 0.3 34000 101 102 103 106 209 304
21 0.5 40000 105 107 203 206 3
32 0.99 34000 11 10 198 99 909 904
32 0.01 36000 141 142 173 9006 809 804 1105 1204
33 0.03 34000 501 502 503 108
33 0.5 47000
42 0.6 34000
42 0.4 70100

In column F I need to make a sum of the values in Column C whe
where columnD =3400 and column D<40000 and column A is equal to any of the
value in GX:NX where X is the row number if column F

The only way I can see to do this is pretty long and messy:
{=SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=g2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=h2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=i2),(C1:C8))+
...
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=n2),(C1:C8))}

Is there a way to simplify this?

thanks.

summed product of multiple arrays
 

Perhaps easier to use an empty col, eg in E1, copied down:
=IF(AND(D1=34000,D1<40000,ISNUMBER(MATCH(A1,G1:N1 ,0))),C1,"")
Then just sum col E.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"koneil" wrote:

I should add in here that the columns are misaligned din my post. For the
first row,
A1=21
B1=""
C1=0.3
D1=3400
E1=""
F1=long formula
G1=101
etc

"koneil" wrote:

I have a spreadsheet that in simplified form looke like this:
A B C D E F G H I J K L M N
21 0.3 34000 101 102 103 106 209 304
21 0.5 40000 105 107 203 206 3
32 0.99 34000 11 10 198 99 909 904
32 0.01 36000 141 142 173 9006 809 804 1105 1204
33 0.03 34000 501 502 503 108
33 0.5 47000
42 0.6 34000
42 0.4 70100

In column F I need to make a sum of the values in Column C whe
where columnD =3400 and column D<40000 and column A is equal to any of the
value in GX:NX where X is the row number if column F

The only way I can see to do this is pretty long and messy:
{=SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=g2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=h2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=i2),(C1:C8))+
...
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=n2),(C1:C8))}

Is there a way to simplify this?

thanks.

summed product of multiple arrays
 

=SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(ISNUMBER(MATCH(A1:A8,G2:N2,0))),C1:C8)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"koneil" wrote in message
...

I should add in here that the columns are misaligned din my post. For the
first row,
A1=21
B1=""
C1=0.3
D1=3400
E1=""
F1=long formula
G1=101
etc

"koneil" wrote:

I have a spreadsheet that in simplified form looke like this:
A B C D E F G H I J K L M
N
21 0.3 34000 101 102 103 106 209 304
21 0.5 40000 105 107 203 206 3
32 0.99 34000 11 10 198 99 909 904
32 0.01 36000 141 142 173 9006 809 804 1105 1204
33 0.03 34000 501 502 503 108
33 0.5 47000
42 0.6 34000
42 0.4 70100

In column F I need to make a sum of the values in Column C whe
where columnD =3400 and column D<40000 and column A is equal to any of
the
value in GX:NX where X is the row number if column F

The only way I can see to do this is pretty long and messy:
{=SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=g2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=h2),(C1:C8))+
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=i2),(C1:C8))+
...
SUMPRODUCT(--(D1:D840000),--(D1:D8<44000),--(A1:A8=n2),(C1:C8))}

Is there a way to simplify this?

thanks.