SUMPRODUCT(C2:C6;--(INDEX(C21:C23,MATCH(A2:A6,A21:23),0))=$A$30))

Aside from the syntax errors, INDEX is only returning a *single* element.

Try this array formula** :

=SUM(IF(ISNUMBER(MATCH(A2:A6,IF(ISNUMBER(MATCH(C21 :C23,A30,0)),A21:A23),0)),C2:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"vsoler" wrote in message
...
Hello,

My problem is the following:

I have a long table of expenses by cost center (CC). Its structure is
(once simplified) like this:

CC Cta Bgt Act
CCa Cta1 1 3
CCb Cta2 2 4
CCc Cta3 3 5
CCa Cta2 4 1
CCc Cta1 5 7

Totals 15 20

(CC= Cost Center, Bgt= Budget, Act=Actual)
Say it is in A1:D6.

On the other side I have a lookup table that defines how I want to
group my CC for analysis:

CC CCNom CCGr1
CCa Centro A Fab
CCb Centro B Adm
CCc Centro C Fab

(CCa and CCc will be grouped into "Fab", CCb will be grouped into
"Adm")
Say it is in A20:C23

Given a certain value in cell A30 (say for example "Fab") I need to
calculate the total value for its costs centers (CCa & CCc). In the
example given it will be 1+3+4+5 = 13

I started with a simple index/match formula, --(INDEX(C21:C23,MATCH
(A2:A6,A21:23),0))=$A$30) that works, since I can see that the result,
using Ctrl-Shft-Enter is {1\0\1\1\1}

The problem comes when I want to multiply this vector by the Bgt.

I would have expected that SUMPRODUCT(C2:C6;--(INDEX(C21:C23,MATCH
(A2:A6,A21:23),0))=$A$30)) would give me the correct result, but it
does not.

What am I doing wrong?

Best regards