VBA or anything to change SUM as columns (not rows) are grouped and ungrouped

phillip87

Board Regular
Joined
Jan 28, 2019
Messages
69
I have a spreadsheet that goes across the screen, this is grouped by month and I need the totals to change when each is grouped or ungrouped.

i.e. B4 shows 1720 which is a sum of data from E4:Z4, as E4:Z4 is grouped the sum of 1720 needs to change and reflect this.

I have a trend line on the left measuring the data which changes as the columns are grouped/ungrouped and this is how I need the cell in B4 to operate
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Formula Driven answer you will need a helper row with the following formula =IF(INDEX(CELL("width",E4),1)=0,0,1)

Then Sumifs() off that row, I put my helper row in Row 2. =SUMIFS($E$4:$Z$4,$E$2:$Z$2,1)

You may need to tweak your Cell formula. Took me 30 minutes to figure out how to prevent #spill errors on this solution. Hope this helps!
 
Upvote 0
Thank you, I will have a look at this and try it now, will be a lot tidier than adding another total row for each month.
 
Upvote 0
Formula Driven answer you will need a helper row with the following formula =IF(INDEX(CELL("width",E4),1)=0,0,1)

Then Sumifs() off that row, I put my helper row in Row 2. =SUMIFS($E$4:$Z$4,$E$2:$Z$2,1)

You may need to tweak your Cell formula. Took me 30 minutes to figure out how to prevent #spill errors on this solution. Hope this helps!
Sorry can you explain the second helper row, I am assuming these helper rows would go in place of my current total? why is it looking into E2:Z2?
 
Upvote 0
The helper column is determining if your columns are hidden/visible. E2:Z2 is giving you a boolean 0,1 answer if the column is hidden. It is being referenced in a SumIfs() looking for 1s. It will sum the same columns where there is a 1 and ignore 0s.

Alternatively you can change the helper formula and sum that row =IF(INDEX(CELL("width",E4),1)=0,0,E4)
 
Upvote 0
The helper column is determining if your columns are hidden/visible. E2:Z2 is giving you a boolean 0,1 answer if the column is hidden. It is being referenced in a SumIfs() looking for 1s. It will sum the same columns where there is a 1 and ignore 0s.

Alternatively you can change the helper formula and sum that row =IF(INDEX(CELL("width",E4),1)=0,0,E4)
I think I get it but I am a little lost as to where I should place things, I have attached a screen shot. the data on the right is what will be hidden "grouped" so just the period total needs to change
 

Attachments

  • Capture.PNG
    Capture.PNG
    44.5 KB · Views: 5
Upvote 0
The helper column is determining if your columns are hidden/visible. E2:Z2 is giving you a boolean 0,1 answer if the column is hidden. It is being referenced in a SumIfs() looking for 1s. It will sum the same columns where there is a 1 and ignore 0s.

Alternatively you can change the helper formula and sum that row =IF(INDEX(CELL("width",E4),1)=0,0,E4)
The helper column is just returning the value on that cell and not a 1/0
 
Upvote 0
Based on your picture in the last post - I would put the helper row in the row above PeriodTotal. The formula might have some issues with the merged cells you have in your sheet.
 
Last edited:
Upvote 0
Solution
Based on your picture in the last post - I would put the helper row in the row above PeriodTotal. The formula might have some issues with the merged cells you have in your sheet.
Ok thank you, yea running the data horizontally has some issues.
 
Upvote 0
Based on your picture in the last post - I would put the helper row in the row above PeriodTotal. The formula might have some issues with the merged cells you have in your sheet.
I did some tweaking and it works perfectly thank you, i tried everywhere on the internet and you are the only one that could make it work haha
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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