V
vsoler
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 A16.
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
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 A16.
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