| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum data that is only within a specific number range?
Hi all:
I need to count the number of rows that have specifiic data in specific columns. There are multiple columns to check for data and all colums must match a specific value or range of values. I have a formula below that works and is close to what I need, but I need to modify the last argument: "(Data!$BB$2:$BB$10000=BW$5)" so that BW$5 can be a number range, for example the following greater than/less than statement would be perfect, 0 <= BW$5 <= 10, but I don't know how to incorporate this into the formula, or how to reference the formula to cells that contain this data. This is my working formula: =SUMPRODUCT((Data!$A$2:$A$10000=$A7)*((Data!$AX$2: $AX$10000=$BW$3)+(Data!$AX$2:$AX$10000=$BW$4))*(Da ta!$BB$2:$BB$10000=BW$5)) Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sum data that is only within a specific number range?
Hi
=SUMPRODUCT(--(Data!$A$2:$A$10000=$A7),--((Data!$AX$2:$AX$10000=$BW$3)+(Data !$AX$2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$ 5),--(Data!$BB$2:$BB$10000 =BW$5),--(Data!$BB$2:$BB$10000<=BW$6)) I.e. you must have 2 cells to refer to - for lower and upper limit respectively. Arvi Laanemets "ryesworld" wrote in message ... Hi all: I need to count the number of rows that have specifiic data in specific columns. There are multiple columns to check for data and all colums must match a specific value or range of values. I have a formula below that works and is close to what I need, but I need to modify the last argument: "(Data!$BB$2:$BB$10000=BW$5)" so that BW$5 can be a number range, for example the following greater than/less than statement would be perfect, 0 <= BW$5 <= 10, but I don't know how to incorporate this into the formula, or how to reference the formula to cells that contain this data. This is my working formula: =SUMPRODUCT((Data!$A$2:$A$10000=$A7)*((Data!$AX$2: $AX$10000=$BW$3)+(Data!$AX $2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$5)) Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sum data that is only within a specific number range?
Hi, I think I see what you're trying to do, but why is BW5 referenced twice?
"...*(Data!$BB$2:$BB$10000=BW$5),--(Data!$BB$2:$BB$10000=BW$5),--(Data$BB$2:$BB$10000<=BW$6))" Thanks, Ryan "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(Data!$A$2:$A$10000=$A7),--((Data!$AX$2:$AX$10000=$BW$3)+(Data !$AX$2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$ 5),--(Data!$BB$2:$BB$10000 =BW$5),--(Data!$BB$2:$BB$10000<=BW$6)) I.e. you must have 2 cells to refer to - for lower and upper limit respectively. Arvi Laanemets "ryesworld" wrote in message ... Hi all: I need to count the number of rows that have specifiic data in specific columns. There are multiple columns to check for data and all colums must match a specific value or range of values. I have a formula below that works and is close to what I need, but I need to modify the last argument: "(Data!$BB$2:$BB$10000=BW$5)" so that BW$5 can be a number range, for example the following greater than/less than statement would be perfect, 0 <= BW$5 <= 10, but I don't know how to incorporate this into the formula, or how to reference the formula to cells that contain this data. This is my working formula: =SUMPRODUCT((Data!$A$2:$A$10000=$A7)*((Data!$AX$2: $AX$10000=$BW$3)+(Data!$AX $2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$5)) Any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to sum data that is only within a specific number range?
I think I figured it out...
=SUMPRODUCT((Data!$A$2:$A$10000=$A7)*((Data!$AX$2: $AX$10000=$BW$3)+(Data!$AX$2:$AX$10000=$BW$4))*(Da ta!$BB$2:$BB$10000=BW$5)*(Data!$BB$2:$BB$10000<=B X$5)) where BW5 and BX5 are the lower and upper limits. Thanks for the hint! Ryan "ryesworld" wrote: Hi, I think I see what you're trying to do, but why is BW5 referenced twice? "...*(Data!$BB$2:$BB$10000=BW$5),--(Data!$BB$2:$BB$10000=BW$5),--(Data$BB$2:$BB$10000<=BW$6))" Thanks, Ryan "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(Data!$A$2:$A$10000=$A7),--((Data!$AX$2:$AX$10000=$BW$3)+(Data !$AX$2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$ 5),--(Data!$BB$2:$BB$10000 =BW$5),--(Data!$BB$2:$BB$10000<=BW$6)) I.e. you must have 2 cells to refer to - for lower and upper limit respectively. Arvi Laanemets "ryesworld" wrote in message ... Hi all: I need to count the number of rows that have specifiic data in specific columns. There are multiple columns to check for data and all colums must match a specific value or range of values. I have a formula below that works and is close to what I need, but I need to modify the last argument: "(Data!$BB$2:$BB$10000=BW$5)" so that BW$5 can be a number range, for example the following greater than/less than statement would be perfect, 0 <= BW$5 <= 10, but I don't know how to incorporate this into the formula, or how to reference the formula to cells that contain this data. This is my working formula: =SUMPRODUCT((Data!$A$2:$A$10000=$A7)*((Data!$AX$2: $AX$10000=$BW$3)+(Data!$AX $2:$AX$10000=$BW$4))*(Data!$BB$2:$BB$10000=BW$5)) Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
| Printing data validation scenarios | Excel Worksheet Functions | |||
| How can I check if data in an external data range is changed afte. | Excel Worksheet Functions | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
| Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
Linear Mode
