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?

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?

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?

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?