Array Formula, noncontigous range
 

Hello,

Application: Excel XP SP2
OS: WIN XP SP 1

Problem:
Is it possible to use an array formula with an noncontigous range,
or is it normal to get "#Value!" error?

Example:

Cells A1, A3, A5, A7 = Range named "Category"
Cells B1, B3, B5, B7 = Range named "Data"

Array Formula:

={SUM((Category="Test")*Data)}

TIA,
Werner

Don't think so as is, but you could try

=SUMPRODUCT(--(MOD(ROW(A1:A8),2)=1),--(A1:A8="Category"),B1:B8)

--
HTH

Bob Phillips

"Werner Rohrmoser" wrote in message
oups.com...
Hello,

Application: Excel XP SP2
OS: WIN XP SP 1

Problem:
Is it possible to use an array formula with an noncontigous range,
or is it normal to get "#Value!" error?

Example:

Cells A1, A3, A5, A7 = Range named "Category"
Cells B1, B3, B5, B7 = Range named "Data"

Array Formula:

={SUM((Category="Test")*Data)}

TIA,
Werner