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

Coontradis

New Member
Joined
Aug 28, 2019
Messages
1
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.:


ABCDEFGHI
1COST CENTER 1000060000REMY25
2JON30
3BOB30
4Result85
560001Hammer10
6Roof1000
7Result1010
8Result1095
9Cost center 2000060000Paul35
10Adele50
11Result85
1260001Belt95
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
Joined
Aug 7, 2019
Messages
23
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:

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
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
Joined
Aug 7, 2019
Messages
23
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
Joined
Oct 17, 2012
Messages
381
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
Joined
Oct 17, 2012
Messages
381
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.

 

Forum statistics

Threads
1,078,467
Messages
5,340,498
Members
399,379
Latest member
Ashrafkamal

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top