SUMPRODUCT #VALUE

  • Thread starter Thread starter Hippolyte
  • Start date Start date
H

Hippolyte

Hi all


Working on Excel2000 I have a function that gives me headache.

The function is:

=SUMPRODUCT(($C$7:$C$10=B$1)*1,$D$7:$D$10,MMULT($F$7:$I$10,TRANSPOSE((F5:I5=$A2)*1)))

Broken down in pieces it gives
=SUMPRODUCT(($C$7:$C$10=B$1)*1,
$D$7:$D$10,
MMULT($F$7:$I$10,TRANSPOSE((F5:I5=$A2)*1)))


For some unknown (to me) reasons, intermediate results work just fine.
I mean
($C$7:$C$10=B$1)*1 give the expected result in an array formula
$D$7:$D$10 of course does too
MMULT($F$7:$I$10,TRANSPOSE((F5:I5=$A2)*1)) does as well

This results in 3 "vertical vectors" that I SUMPRODUCT. It works.

But when I put all in one formula (the big formuala above), the
formula "wizard" to build the SUMPRODUCT
gives the correct result but the cell shows #VALUE.

Don't know how clear this is but you can always take a look at
www.5l.free.fr/book1.xls



Maybe it's so obvious that I don't see it but help would be
appreciated.


Jerome
 
Hi,

Try to enter the formula as an array formula by pressing
ctrl+shift+enter when entering the formula.

This is because MMULT is a part that needs to be entered that way.

- Asser
 
Nope that doesn't do it!

The result is actually a number and when auditing formula
is gives no errors. Weird still.

Thanks anyway,
Jerome
 
Jerome,

It works for me if I array enter, it gives 1500.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hmm,

as I enter it as an array formula, I get the answer 1500. I haven'
checked if it is or should be the correct answer, but that's what
get. I use finnish regional settings and XL2003. Maybe those hav
something to do with this.

I'll check this later again if I have some time.

- Asse
 
1500. Yes, that's the value I'd like to appear in the cell.
Hmm, maybe it's the way I enter the array formula. I
checked on an excel XP box an the error is the same.

So that's definately an error in my formula.

ExcelXP evaluates the error being in the TRANSPOSE.

In TRANSPOSE((G5:J5=$A8)*1) G5:J5 is evaluated as a #value.


So I'm curious to see your formula and know which part you
enter as an array.


Thanks for your time,
Jerome
 
Thanks you for your help.
The array thing finally got to my brain. And guess what?
It works!




Thank you,
Jerome
 
Anonymous said:
*ExcelXP evaluates the error being in the TRANSPOSE.

In TRANSPOSE((G5:J5=$A8)*1) G5:J5 is evaluated as a #value.*
Click to expand...

Hi,

That is exactly the error that evaluation gives if the formula is NOT
entered as an array formula. When it is an array, there is no error.

So ones more to enter a formula as an array formula, you should press
ctrl+shift+enter. In your example:

- select cell B8
- press F2 (to edit the cell)
- press ctrl+shift+enter simultaniously

Now the answer should be 1500. If you look at the formula bar, there
should be something like

{=SUMPRODUCT(($D$8:$D$11=B$7)*1,$E$8:$E$11,MMULT($G$8:$J$11,TRANSPOSE((G5:J5=$A8)*1)))}

The { and } symbols shows that the formula is in array "mode"

- Asser
 
Back
Top