summing based on multiple array criteria

[Guest]

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)

 

[Guest]

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

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

You may need to activate this with CTRL SHIFT ENTER

Let me know.

 

[Guest]

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

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

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

You may need to activate this with CTRL SHIFT ENTER

Let me know.

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)

 

[Guest]

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

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

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:C10>40000),(B1:B10))

You may need to activate this with CTRL SHIFT ENTER

Let me know.

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)