summarize table values in one cell while being resistant to column, row changes

excelling_

New Member
Joined
Jan 1, 2022
Messages
1
Office Version
  1. 2013
Hello, I would like to use this excel table and make my changes: add/del table rows, add/del table columns and still receive summarized results (concatenated to 1 cell with delimiter of my choosing) on the sides like in the image. there are two kinds of summaries im looking for.
On I2:J6 its summarizing by row, in one cell, concatenated by a comma character. It says for each sub, what multi it is in
On D8:G9 its summarizing by column, in one cell, concatenated in the same way. Its saying what is in each multi: sub1, sub2, sub3, etc. I have moved the cells to be in column D just so its easier to see the results without wrapping, but it should be under D8:G9, as if its corresponding to each column under the table, like in image 2.

I will be doing data manipulation in the table mainly: adding/removing x's, adding/deleting rows to the table, adding,deleting columns to the table. filtering, sorting the table as well

What i tried to do (and maybe this should be ignored) was use some helper cells but I noticed that when i manipulated the table rows, columns, the helper cells formulas would break, and my summarized concatenated cell would be wrong.
example formulas I used:
if table is on A6:BC1800, and actual data is on D7:BC1800 then I put on BD7 =IF(ISBLANK(D$7),"",D$7&"+") and BD8 =BD7&IF(ISBLANK(D8),"",D8&"+") filled down to BD1800. Here instead of x's I had the sub# repeated in the cell, then on BD1800, i would have a concatenated result for column D. I found that using sub# in each cell was inefficient so I want to use x's instead.
So then I am working on a minitiarized example sheet I attached an image of here and I was able to get a partial summary on the right of the table in I2:J6 using Index/Match return column header (multiple occurrences) but its a fill to the right type of formula. The problem is I won't know how many cells I have to fill to the right? and that changes in the table break the formulas, and also that I'm looking for results in one cell instead.
I also messed around with some offset() type of formulas but I'm not sure how to do it with a table with amorphous rows, columns.

what could work for this? appreciate any response
 

Attachments

  • 627Bgv7.png
    627Bgv7.png
    35.4 KB · Views: 10
  • VY69312.png
    VY69312.png
    27.1 KB · Views: 10

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,214,833
Messages
6,121,858
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top