Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing based on a range of criteria | Excel Discussion (Misc queries) | |||
Summing a range based on value criteria | Excel Discussion (Misc queries) | |||
Summing based on criteria | Excel Worksheet Functions | |||
Summing quantities based on like criteria? | Excel Discussion (Misc queries) | |||
Summing based on 2 criteria | Excel Discussion (Misc queries) |