Sumproduct

[Barb Reinhardt]

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_Date>F$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

 

[T. Valko]

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_Date>F$3),--('2009'!Due_Date<G$3))

 

[Barb Reinhardt]

'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.

 

[Barb Reinhardt]

Never mind. I got rid of the N/A#'s in my source data.

 

[T. Valko]

Ok, try this array formula** :

=SUM(IF('2009'!Region_All=$A31,IF(ISNUMBER('2009'!Due_Date),('2009'Due_Date>F$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.

 

[Barb Reinhardt]

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]

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.