# Index, Offset, Index... I'm getting crazy

##### New Member
For the pros…
Been working on this for the last day….
I have a monthly report that update each week. It consists of cost center and account number. I want to control the budget and have the total of each account for each cost center. Not very complicated up to now… Here is the fun part: I have 5 cost center. In each cost center I have accounts. The accounts numbers are the same for all cost center. Ex: account 60000 is for my labor, so I have a 60000 account in my production cost center, service, office, sales and maintenance.
I want to have a tab for each of my cost center that give me the total of the account. But here is how the data are coming from the download.:

 A B C D E F G H I 1 COST CENTER 10000 60000 REMY 25 2 JON 30 3 BOB 30 4 Result 85 5 60001 Hammer 10 6 Roof 1000 7 Result 1010 8 Result 1095 9 Cost center 20000 60000 Paul 35 10 Adele 50 11 Result 85 12 60001 Belt 95 13 14

<tbody>
</tbody>

SO for account 60000 (column C) for cost center 10000 (column B) I want the total as show in row 4 column I (total by account for a specific cost center).
The data of the cost center are always in column B but appear only once where the data for this cost center start. The account stands in column C and the total in I.
I would like to have the total of each account in another tab (on for each cost center). Note that the data add every week so this need to be dynamic. I also work with excel 2016

Thanks for helping me

#### ekrause

##### New Member
I created a formula that works based on the table your provided but can't guarantee it'll work with more data.

=INDEX(OFFSET(\$I\$1,MATCH(\$K2,\$B:\$B,0)-1+MATCH(\$L2,OFFSET(\$C\$1,MATCH(\$K2,\$B:\$B,0)-1,0,COUNT(\$I:\$I)),0)-1,0,COUNT(\$I:\$I)),MATCH("result",OFFSET(\$E\$1,MATCH(\$K2,\$B:\$B,0)-1+MATCH(\$L2,OFFSET(\$C\$1,MATCH(\$K2,\$B:\$B,0)-1,0,COUNT(\$I:\$I)),0)-1,0,COUNT(\$I:\$I)),0),1)

Here are the results this generates with the table starting on cell K2. A few notes: I changed Bob's value to 35 so there was a difference between Cost Center 10000 & 20000 Results & added a result value for 60001 for just the Belt.

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

While this formula MAY work I would suggest making changes to the structure of your data before trying to summarize it.

Hopefully someone else has a more elegant solution.

Edit: Updated Table starting cell

Last edited:

#### silentwolf

##### Well-known Member
Hi,

you can do this kind of operation in both columns like Column(C) and Column(D) then you can filter your data like "COST CENTRE 100000 and 60001" OR "COST CENTRE 10000 AND 60002" or what ever.. then copy the filtered data to new worksheet or
simple add a sum to the table.

HTH

#### Sam_D_Ben

##### Active Member
I created a formula that works based on the table your provided but can't guarantee it'll work with more data.

=INDEX(OFFSET(\$I\$1,MATCH(\$K2,\$B:\$B,0)-1+MATCH(\$L2,OFFSET(\$C\$1,MATCH(\$K2,\$B:\$B,0)-1,0,COUNT(\$I:\$I)),0)-1,0,COUNT(\$I:\$I)),MATCH("result",OFFSET(\$E\$1,MATCH(\$K2,\$B:\$B,0)-1+MATCH(\$L2,OFFSET(\$C\$1,MATCH(\$K2,\$B:\$B,0)-1,0,COUNT(\$I:\$I)),0)-1,0,COUNT(\$I:\$I)),0),1)
Edit: Updated Table starting cell
Would be helpful if you could frame a table along with the position of your formula. Appreciate your effort.

#### ekrause

##### New Member
Would be helpful if you could frame a table along with the position of your formula. Appreciate your effort.
I used the table as framed in original post. Also referenced the start of my table (cell K2) and the table I made in my post:

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

Literally not sure what else I could've done aside from posting the Excel I created this in.

#### Sam_D_Ben

##### Active Member
I used the table as framed in original post. Also referenced the start of my table (cell K2) and the table I made in my post:

COST CENTER 10000 60000 90
COST CENTER 10000 60001 1010
Cost center 20000 60000 85
Cost center 20000 60001 95

Literally not sure what else I could've done aside from posting the Excel I created this in.
My mistake i dint see it. I appreciate it. Also Pivot helps us to get detailed output.

#### Sam_D_Ben

##### Active Member
I checked it with more datas. All we have to do is just adding up data daily in the same format and just refreshing the PIVOT gives the desired detailed result.

1,082,023
Messages
5,362,725
Members
400,687
Latest member
jomelprototype

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...