sumproduct with criteria and multiply problem

[mohavv]

Hi,

I've got the following formula which isn't working (gives #VALUE!).

=SUMPRODUCT(--(A13:A471=F476&G475),(E13:N471),(P13:Y471))

Is it possible to have one criteria and have two ranges multiply?

If so, what am I doing wrong?

Cheers,

Harold

 

[Rick Rothstein]

You are going to have to tells in words what you hoped that formula was
going to do. The reason for the error is your arrays are not the same size
A13:A471 has less elements in it than either E13:N471 or P13:Y471... each
array must be the same size.

 

[T. Valko]

Try it like this:

=SUMPRODUCT((A13:A471=F476&G475)*E13:N471*P13:Y471)

However, if there is any text entries in the ranges E13:N471and P13:Y471 it
won't work. You'll still get a #VALUE! error.

 

[Rick Rothstein]

Okay, so this is a 3D array calculation in which case your ranges may be
okay. However, I'd still like to see a description of what it is you are
expecting the formula to do.

 

[mohavv]

Try it like this:

=SUMPRODUCT((A13:A471=F476&G475)*E13:N471*P13:Y471)

However, if there is any text entries in the ranges E13:N471and P13:Y471 it
won't work. You'll still get a #VALUE! error.

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thanks!

Last one is working fine, can't understand why though.

I was trying to calculate an average price over multiple products
which are listed in colums, in rows I have delivery weeks per region.
Range one has volumes range two has price.

Cheers,

Harold

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try it like this:

=SUMPRODUCT((A13:A471=F476&G475)*E13:N471*P13:Y471)

However, if there is any text entries in the ranges E13:N471and P13:Y471
it
won't work. You'll still get a #VALUE! error.

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thanks!

Last one is working fine, can't understand why though.

I was trying to calculate an average price over multiple products
which are listed in colums, in rows I have delivery weeks per region.
Range one has volumes range two has price.

Cheers,

Harold