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

[Coontradis]

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.:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]COST CENTER 10000[/TD]
[TD]60000[/TD]
[TD][/TD]
[TD][/TD]
[TD]REMY[/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JON[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BOB[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]60001[/TD]
[TD][/TD]
[TD][/TD]
[TD]Hammer[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Roof[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1010[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1095[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Cost center 20000[/TD]
[TD]60000[/TD]
[TD][/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Adele[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]60001[/TD]
[TD][/TD]
[TD][/TD]
[TD]Belt[/TD]
[TD][/TD]
[TD][/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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

 

[silentwolf]

Hi,
perhabs this link will help you.

https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

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]

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]

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]

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]

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.