summing based on multiple array criteria
 

For a simplification, I have the following:

CID=[32,33,34,35,36,37,38,39]

DATA:

A B C
21 .30 34000
21 .50 40000
32 .99 34000
32 .01 36000
33 .03 34000
33 .50 47010
42 .60 34000
42 .40 70100

I need to set up a formula to sum up the total in column "B" for all cases
where the value in column "A" is equal to any of the values in CID and the
value in column "C" is greater than 30000 and less than 40000. (Here, then
the answer would be 1.03)

summing based on multiple array criteria
 

I've not tried this with arrays, so I'm not sure if it will work

=sumproduct(--(A1:A10=CID),--(C1:C10<40000),--(C1:C1040000),(B1:B10))

You may need to activate this with CTRL SHIFT ENTER

Let me know.


"koneil" wrote:

For a simplification, I have the following:

CID=[32,33,34,35,36,37,38,39]

DATA:

A B C
21 .30 34000
21 .50 40000
32 .99 34000
32 .01 36000
33 .03 34000
33 .50 47010
42 .60 34000
42 .40 70100

I need to set up a formula to sum up the total in column "B" for all cases
where the value in column "A" is equal to any of the values in CID and the
value in column "C" is greater than 30000 and less than 40000. (Here, then
the answer would be 1.03)

summing based on multiple array criteria
 

Barb,

Thanks for the help but I'm afraid that forumal doesn't work - I just get a
#NA error when I try it.

Karen


"Barb Reinhardt" wrote:

I've not tried this with arrays, so I'm not sure if it will work

=sumproduct(--(A1:A10=CID),--(C1:C10<40000),--(C1:C1040000),(B1:B10))

You may need to activate this with CTRL SHIFT ENTER

Let me know.


"koneil" wrote:

For a simplification, I have the following:

CID=[32,33,34,35,36,37,38,39]

DATA:

A B C
21 .30 34000
21 .50 40000
32 .99 34000
32 .01 36000
33 .03 34000
33 .50 47010
42 .60 34000
42 .40 70100

I need to set up a formula to sum up the total in column "B" for all cases
where the value in column "A" is equal to any of the values in CID and the
value in column "C" is greater than 30000 and less than 40000. (Here, then
the answer would be 1.03)

summing based on multiple array criteria
 

Do you have NA() in one of the columns of data? If so, which one?


"koneil" wrote:

Barb,

Thanks for the help but I'm afraid that forumal doesn't work - I just get a
#NA error when I try it.

Karen


"Barb Reinhardt" wrote:

I've not tried this with arrays, so I'm not sure if it will work

=sumproduct(--(A1:A10=CID),--(ISNUMBER(C1:C10),--(C1:C10<40000),--(C1:C1040000),(B1:B10))

You may need to activate this with CTRL SHIFT ENTER

Let me know.


"koneil" wrote:

For a simplification, I have the following:

CID=[32,33,34,35,36,37,38,39]

DATA:

A B C
21 .30 34000
21 .50 40000
32 .99 34000
32 .01 36000
33 .03 34000
33 .50 47010
42 .60 34000
42 .40 70100

I need to set up a formula to sum up the total in column "B" for all cases
where the value in column "A" is equal to any of the values in CID and the
value in column "C" is greater than 30000 and less than 40000. (Here, then
the answer would be 1.03)