Sum/Index based on merged cell data

[Dulanic]

I couldn't think of a good way to expain this better in the title. Basically I have merged cells in column A... I need to calculate all of the rows in Column C based on the merged string in Column A. Normally I would use a INDEX and MATCH combo but this is throwing me for a loop.... index will find the start of the merged cell, but how can I get the row value of the end of the merged cell to work into the formula? Attached is my data layout.

VBA is not an option for this even though I would have no issue writing up quick VBA to do it. Oh also the length of the merged cells vary... so I am not being literal in my descrption inside the example. Team 1 could be 10 people and team 2 100.

Thanks in advance for any help!

[NBVC]

If you put Team 1 in B19, then you can try:

=SUM(OFFSET(INDEX($A$1:$A$12,MATCH(B19,$A$1:$A$12,0)),0,2,6,1))

copied down.

[TMS]

What can I say? Don't use Merged Cells?

However, you can still do it with a helper column

in D2, put: =IF(A2<>0,A2,D1) and copy it down

Cell C20: =SUMIF($D$2:$D$16,A20,$C$2:$C$16)


Regards

[Dulanic]

If you put Team 1 in B19, then you can try:

=SUM(OFFSET(INDEX($A$1:$A$12,MATCH(B19,$A$1:$A$12,0)),0,2,6,1))

copied down.

Works somewhat, but the 6 is variable. The team is a variable length and can change from day to day. The data also exists on another worksheet. I attached a updated workbook to hopefully clarify.

[Dulanic]

What can I say? Don't use Merged Cells?

However, you can still do it with a helper column

in D2, put: =IF(A2<>0,A2,D1) and copy it down

Cell C20: =SUMIF($D$2:$D$16,A20,$C$2:$C$16)


Regards

It isn't by choice trust me. It is a output file from another system that I can't control how the data is sent out which also prevents helper columns. I guess I could refer to VBA in the first file to calculate on the output file :/ Uggg I hate badly laid out data!

[Dulanic]

Grasping straws here...maybe this will help. I could unmerge Column A which would leave team 1 at the top of each team and blanks for each row until the next team was started. Not sure if that would help or not but it is a quick thing I could have done every time the file was saved would be unmerge that column.

[NBVC]

Unmerging and leaving blanks will be the same thing....

So you can do as TMShucks suggests, or just fill in the team name in every cell in column A and use simple SUMIF.

Or if you enter formula in B3 of sheet 2:

=SUM(INDEX(Sheet2!$C$1:$C$11,MATCH(A3,Sheet2!$A$1:$A$11,0)):Sheet2!$C$11)

then in B2: =SUM(INDEX(Sheet2!$C$1:$C$11,MATCH(A2,Sheet2!$A$1:$A$11,0)):Sheet2!$C$11)-SUM(B3:B$3)

and copy up to B1.

So start formula in last team's cell, then in next up add the subtraction of the last cell result and copy up.

[Dulanic]

Thanks... I will have to think this through. I can't really unmerge and populate all the cells as there can be hundreds of "teams" (not really teams) and it would take a long time. I will have to come up with something I can run daily that will only take seconds as I can't pass the data along as others that need the data are too.... stupid to know how to enable macros in excel and they always break things.

My biggest hurdle will be the data above and below can't be touched but I think I can start once it finds a certain value in A and end when it finds another value in A. I kind of thought the merged data would throw a wrench in this, but I have seen you guys pull of some crazy stuff before, so thought I would try that first

[NBVC]

Does my formula solution above work, then... it doesn't require you change your setup...

[WHER]

Another approach with a helper column.

[Dulanic]

Does my formula solution above work, then... it doesn't require you change your setup...

Not quite. It is my own fault for not explaining it well enough. I am calculating Team 2 for example. Team 1 and Team 3 don't get calculated and their names are variable and could be hfkhjksdf and lksjkf insted of team 1 and 3.

[Dulanic]

Another approach with a helper column.

Can't change the data as I won't be the one saving the data

[NBVC]

Not quite. It is my own fault for not explaining it well enough. I am calculating Team 2 for example. Team 1 and Team 3 don't get calculated and their names are variable and could be hfkhjksdf and lksjkf insted of team 1 and 3.

Don't really understand that...

can you create a more realistic representation of what you mean and what you expect as a result?

The formula I gave is independent of actual team name or numbering or whatever... as long as the list in sheet B includes all the team names and is in same order as in Sheet1, I think it should work....

[Dulanic]

Don't really understand that...

can you create a more realistic representation of what you mean and what you expect as a result?

The formula I gave is independent of actual team name or numbering or whatever... as long as the list in sheet B includes all the team names and is in same order as in Sheet1, I think it should work....

Sorry for the bad explanation. Formula goes in Book 2 and data is in book 3. Sorry I thought my explanation would work, but now I see why it didn't. Normally I adjust what formula is given to me to work across workbooks but this wasn't working for me

[NBVC]

Well see the merged cells or missing cell contents make like not fun... and since you can't touch the workbook, I wonder if you can just copy over the table dynamically,

i.e. in say N2, enter simply:

=[Book3.xlsx]Sheet1!A1

copied down and across as far as you want.

Then you can apply the helper column that TMshucks referred to (i.e in O2, =IF(L2<>0,L2,O1) copied down).. the and use SUMIF (=SUMIF(O:O,A1,N:N))

[Billal]

sum merged1.png
here's how i did it,
i have unequally sized merged cells in column A with machine names,product names in B product weights in C and now i want machine total in D from rows in column C corresponding to merged cell in Column A
1)First i put =IF(A2<>0,1,0) in F2 then drag it down through all rows giving me a series of 1s and 0s 1 being first row of merged cells,
2)Next i put =SUM(A$2:A2) in G2 and dragged it through all rows, this gave me 1 for all rows of first merged cell 2 for all rows of 2nd merged cell and so on,
3)Next i used =SUMIF(G$2:G$13,G2,C$2:C$13) in H2 and dragged it down to have sums of all rows corresponding to each merged cells in all the corresponding rows,
4)Next i put =IF(A2<>0,H2," "), in D2(original column where I wanted the sums) an dragged it down to have each sum only once only in first row of merged cell.

Hope someone finds this helpuful as this is major bump, but i was looking for a solution myself but couldn't find one online so came u with this work around.