How to sum data that is only within a specific number range?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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))
Click to expand...

I.e. you must have 2 cells to refer to - for lower and upper limit
respectively.


Arvi Laanemets
 
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
 
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))*(Data!$BB$2:$BB$10000>=BW$5)*(Data!$BB$2:$BB$10000<=BX$5))

where BW5 and BX5 are the lower and upper limits. Thanks for the hint!

Ryan
 
Back
Top