Let's see if I understand this...
Only count those rows where the *specific range* is 201 and 207?
So, if a range is 197 to 203 don't include this row even though 201 to 203
falls within the range?
If the range is 202 to 208 don't include this row even though 202 to 207
falls within the range?
Or, DO count those rows?
Sam, your posts are *always* the most complex posts, bar none! <g
I often wonder what kind of application you're working with and if it can be
made simpler!
--
Biff
Microsoft Excel MVP
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:7ea9029386f78@uwe...
Hi Biff,
Thank you for further input. When I use the formula below on the very
small
sample data it provides the correct result. However, when I apply it to
the
real data (2000 rows, 10 columns), amending the columns and rows; I do not
get the expected results. The counts are much lower than they should be.
Formula used on Sample Data using 3 columns each for "Refs" and "Data":
=SUMPRODUCT(--(MMULT(--(Refs=D2)*(Refs<=E2)*(Data=7),{1;1;1})=2))
Range is 201-207; columns D2 and E2 respectively.
In the Define Name Refers To box "Refs":
=OFFSET(Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$10,MATCH( 9.9E+307,Sheet2!$A$2:$A$10))
,0,0,,3)
In the Define Name Refers To box "Data":
=OFFSET(Sheet2!$F$2:INDEX(Sheet2!$F$2:$F$10,MATCH( 9.9E+307,Sheet2!$F$2:$F$10))
,0,0,,3)
Sample Data:
Refs Refs Refs Range Range Data Data Data
201 205 206 201 207 5 7 7
216 218 220 215 221 13 8 13
243 250 256 250 256 23 53 20
209 211 214 208 214 54 6 54
234 235 243 229 235 84 34 84
205 207 214 201 207 7 7 4
I'm clutching at straws, haven't a clue why it's not working on the live
data.
Does it need ROW(Data)-MIN(ROW(Data)) ?
Further assistance most appreciated.
Cheers,
Sam
T. Valko wrote:
Just replace the references with the named ranges. You can name the
"range"
like: Rng1 and Rng2.
You mentioned that your actual data was 10 columns wide so you need 10
ones
he {1;1;1;1;1;1....}. This could be calculated (adding compexity and
makes the formula an array and longer) but since the number of columns is
relatively small I'd just use the array constant.
=SUMPRODUCT(--(MMULT((refs=rng1)*(refs<=rng2)*(data=7),{1;1;1;1 ;1;1;1;1;1;1})=2))
Also note, the MMULT function is limited to no more than 5460 rows. If
your
data will exced that limit then it's back to the drawing board and will
probably need a helper column.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200801/1