Errata....

I wrote:
"T. Valko" wrote:
Try it like this...
[....]
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417)
Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
error.
[....]
Since the OP was complaining of #VALUE errors, I don't see why you would
steer him in a direction that might exacerbate the problem.

My mistake: I see now that your formulation might solve the OP's problem.
At least it does address the original reason for the #VALUE error. Mea
culpa!

I would have explained that the original #VALUE error resulted from the fact
that the dimensions of the arrays were incompatible. As the SUMPRODUCT Help
page explains: "The array arguments must have the same dimensions. If they
do not, SUMPRODUCT returns the #VALUE! error value."

It might be helpful to Michele. It certainly would have avoided my mistake.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"T. Valko" wrote:
Try it like this...
[....]
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417)
Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
error.

.... Which we can avoid by using something like the original form, namely:

=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417)

Since the OP was complaining of #VALUE errors, I don't see why you would
steer him in a direction that might exacerbate the problem.


----- original message -----

"T. Valko" wrote in message
...
Try it like this...

Just a normal ENTER will do. No need to array enter.

=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417)

Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
error.

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
Hi All,

Here's another I hope you can help with.

Here's my formula that works fine:

{=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D
$6:$D$417)}

Now I want to change the end to ,$D$6:$O$417)}, but changing the D to
an O gives the old #VALUE!.

I don't suspect the cells in the formula because I'm using them with
other similar formulas.

Any help would be appreciated.

Thanks,

Michele