Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up in a table
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivit table- cell on spread sheet referencing pivot table field | Charts and Charting in Excel | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
Insert rows in linked table that are added to secondary table | Excel Discussion (Misc queries) | |||
Excel Pivot Table Plugin? (crosstab to data table) | Excel Discussion (Misc queries) | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions |