Sum Unshaded Cells Above

[ETC]

I have a large workbook with sections of un-shaded columns and shaded cells where I want to Sum the un-shaded section above. There are 126 shaded sections and clicking Sum and dragging the summed cell to the right 3 cells becomes tedious and the formula has to be updated when rows are added or deleted. Is there a faster more efficient way?

 

[vletm]

ETC
As Your sheets layout is as above ...
I would do it this way ...
> There is [x]-button in Cell B1 >> click it

Usage:
Copy Your data to that sheet or copy that macro to Your file (that's all).
You can have as many 'unshaded cell rows' as needed to get those sums.
You can add/delete rows as You need; keep layout same as in Your sample.
You'll get totals in the bottom.

 

[Chihiro]

You could use same formula in all the shaded area.

Ex:
In B8:
=SUM(INDEX(B:B,IFERROR(AGGREGATE(14,6,ROW($A$1:A7)/($A$1:A7=""),1)+1,2)):OFFSET(B8,-1,))

Copy to all other shaded area with formula in it. (i.e. copy across, then filter by colour on Column A. Copy B8:D8 and paste to filtered range).

See attached.

Edit: For total @ row 1731. Use following.
=SUBTOTAL(9,B2:OFFSET(B1731,-1,))

 

[ETC]

ETC
As Your sheets layout is as above ...
I would do it this way ...
> There is [x]-button in Cell B1 >> click it

Thanks. I like both solutions.

 

[ETC]

ETC
As Your sheets layout is as above ...
I would do it this way ...
> There is [x]-button in Cell B1 >> click it

You could use same formula in all the shaded area.

Ex:
In B8:
=SUM(INDEX(B:B,IFERROR(AGGREGATE(14,6,ROW($A$1:A7)/($A$1:A7=""),1)+1,2)):OFFSET(B8,-1,))

Copy to all other shaded area with formula in it. (i.e. copy across, then filter by color on Column A. Copy B8:D8 and paste to filtered range).

See attached.

Edit: For total @ row 1731. Use following.
=SUBTOTAL(9,B2:OFFSET(B1731,-1,))

 

[ETC]

Thanks for the formula solution and the total at the bottom.