Biff,

Thanks for the feedback. I will give that a try this afternoon and will let
you know how it works. I have not tried dynamic ranges. This has been a
work in progress with me ratcheting up my Excel knowledge along the way. I
can see where dynamic ranges would help in several areas of this project and
I need to get up to speed on its use. I can't tell you how much I have
learned from this discussion group and how much I appreciate each of you that
spend so much time responding. My sincere appreciation to you all who give
so much.
--
Thanks,
Nick


"Biff" wrote:

Ok, now I'm confused!!

Try this: (normally entered, not an array like your formula)

=SUMPRODUCT(--('CDR Data'!B4:INDEX('CDR Data'!B:B,L3-3)=M39),--('CDR
Data'!O4:INDEX('CDR Data'!O:O,L3-3)=M36),'CDR Data'!E4:INDEX('CDR
Data'!E:E,L3-3))

Since your ranges start in row 4 the offset is 3 (rows 1, 2, 3). You can see
how I subtracted that from L3. If I have it backwards then just change the
the offset to +3.

Have you considered using dynamic ranges?

http://contextures.com/xlNames01.html#Dynamic

Biff

"Nick" wrote in message
...
Biff, I think I misunderstood your point when I responded. Yes, 17424
would
be the RELATIVE row reference. That is, the row number where the last
data
element is found which is actually four rows more than the number of data
elements.
--
Thanks,
Nick


"Biff" wrote:

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference.
MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets
to
slice and dice a fair amount of raw data. The ending row (17424)
changes
each
month. The references to row 17424 is calculated in another cell
($L$3)
using the MATCH function. I would like to substitute the referenced
cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now
creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick