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?
|