sumproduct with criteria and multiply problem

M

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
 
R

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

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

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

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 -
Click to expand...

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

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 -
Click to expand...

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
 
Top