I have a feeling I can't use array formulas with
dynamic ranges defined this way.

You can as long as each dynamic range is properly defined to be the same
size. For example, Region_All must be the same size as Due_Date.

I see in your other reply you got rid of the #N/A errors. That is usually
the best option but sometimes you might want the errors for whatever reason
and in these cases it's good to have/know a way to account for those in
formulas.

--
Biff
Microsoft Excel MVP


"Barb Reinhardt" wrote in message
...
I think I may remember the issue. The dynamic ranges are defined with
OFFSET
and I have a feeling I can't use array formulas with dynamic ranges
defined
this way.

Thanks for your help.

"T. Valko" wrote:

Ok, try this array formula** :

=SUM(IF('2009'!Region_All=$A31,IF(ISNUMBER('2009'! Due_Date),('2009'Due_DateF$3)*('2009'!Due_Date<G$ 3))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Barb Reinhardt" wrote in
message
...
'Assuming the N/A is a TEXT string and not the error #N/A.

I wish we could assume this, but I've got the error #N/A in those
cells.

"T. Valko" wrote:

in Due_Date I've got numerics and N/A.

Assuming the N/A is a TEXT string and not the error #N/A.

Use another cell to calculate < F3+2 months:

=EDATE(F3,2)

EDATE requires the Analysis ToolPak add-in be installed for Excel
versions
prior to Excel 2007. If you get a #NAME? error see Excel help on the
EDATE
function and it'll tell you how to correct it.

Assume that formula is in G3.

Then:

=SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3))

--
Biff
Microsoft Excel MVP


"Barb Reinhardt" wrote in
message
...
I've got a fairly complicated sumproduct and I can't seem to get
anything
but
a value error.

This is what I have

=SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3))))

The named ranges are dynamic ranges
I've got the region part working.
What I want to find is data in the range Due_Date that is than F3
and
<
F3
+ 2 months. I just can't figure it out. Also, in Due_Date, I've
got
numerics and N/A.

Can Someone assist?

Thanks,
Barb Reinhardt