summed product of multiple arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

koneil - 09 Apr 2007 11:16 GMT

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 where:
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:D8>40000),--(D1:D8<44000),--(A1:A8=g2),(C1:C8))+
SUMPRODUCT(--(D1:D8>40000),--(D1:D8<44000),--(A1:A8=h2),(C1:C8))+
SUMPRODUCT(--(D1:D8>40000),--(D1:D8<44000),--(A1:A8=i2),(C1:C8))+
...
SUMPRODUCT(--(D1:D8>40000),--(D1:D8<44000),--(A1:A8=n2),(C1:C8))}

Is there a way to simplify this?

thanks.

Reply to this Message

koneil - 09 Apr 2007 11:40 GMT

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

> 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
[quoted text clipped - 21 lines]
>
> thanks.

Reply to this Message

Max - 09 Apr 2007 12:10 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> I should add in here that the columns are misaligned din my post. For the
[quoted text clipped - 33 lines]
> >
> > thanks.

Reply to this Message

Bob Phillips - 09 Apr 2007 12:50 GMT

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

Signature

HTH

Bob

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

> I should add in here that the columns are misaligned din my post. For the
> first row,
[quoted text clipped - 34 lines]
>>
>> thanks.

Reply to this Message