What is in WorkSheet!J$1:J$370? Text? That is the impression I got from your
first posting. If it is text, you can't extract it the way you are trying to
by using the SUMPRODUCT function. SUMPRODUCT is basically a mathematical
function (SUM... PRODUCT), each of its parts must ultimately be a numerical
value of some sort.

Rick


"RobN" wrote in message
...
Biff, you've been a great help.

After reading through part of the cpearson site, to try and understand
this, I modified my formula......
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!J$1:J$370)
to an array formula........
=SUM((WorkSheet!$A$1:$A$370=$B9)*(WorkSheet!$C$1:$ C$370=$C9)*WorkSheet!J$1:J$370)
BUT I get a #Value error. I suspect it has something to do with the last
part, "WorkSheet!J$1:J$370", but I don't know what or why, as both the
other sections have a TRUE at the correct position.

Can you see why that would be?

Rob

"T. Valko" wrote in message
...
Technically, *both* formulas are formulas that work with (manipulate)
arrays. The difference is that the INDEX formula needs to be array
entered (CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not.

The programmer that developed the SUMPRODUCT function wrote this array
processing functionality directly into the function code. The INDEX
formula could also be written in such a way that it does not need to be
array entered:

=INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0))

See if this helps:

http://www.cpearson.com/Excel/ArrayFormulas.aspx



--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Biff,

Are you able to explain why this formula is an array formula
=INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0))

AND

why this one is not?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I
don't really understand arrays so your explanation using these
examplesmay help.Rob"T. Valko" wrote in
.. . You're welcome.
Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN"
wrote in
... Thanks Biff.
Worked great!! I Just added an IfError to
get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),)
Rob "T. Valko" wrote in
. .. Try this array
formula**
:=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0))
** array formulas need to be entered using the key combination
ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft
Excel MVP "RobN" wrote in
. .. Is it possible
to use something like this formula, amended as required,to have it
display the text from the cell where the first two parts of theformula
are
true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please showwhat
the formula would need to be? Rob