Macro help: Sum only visible, non-sequential cells across columns

[EricJames]

Hi,

I maintain a budget document and one sheet in the workbook displays Budget, Actual, and Difference values for each month. The last three columns show YTD sums for Budget, Actual, and Difference. (See the image for help visualizing.)
My predecessor apparently would essentially start from scratch for each month's totals, using simple SUM formulas in the YTD that she updated at the end of each month. I stubbornly (perhaps too much) believe that there is a better way to do this, in which I can build formulas for every column in every month that reference Budget and Actual sheets, so the numbers feed in automatically (which I have done already); then, for the YTD section, I insert a formula that adds up only the visible cells (as you can see, I have each month grouped so I can hide the future months). The end result would be a dream world in which I plug numbers into the Actual column, hide the months I don't want to total, and the YTD section reflects the correct totals.

I've searched around for SumVisible VBA macros, which do half of the job for me. The problem is that I'm trying to sum non-sequential cells (Budget+Budget, Actual+Actual, etc), so the usual SumVisible(range) setup doesn't work. I've tested it by CTRL-clicking only the cells I want to sum, for example SumVisible(A1,C1,E1), and that returns a VALUE error. I have no prior experience with excel macros, but I do know a little about programming logic so I tried to tinker with the macro to make it do what I want. With the following setup, it no longer returns an error...rather, it only returns a value of 0. Any help on making this work would be appreciated.

sheet image.png

[walruseggman]

So the way your code is structured, x is determined by the value of the LAST cell in Reference. If it's 1, x will be 1. If it's not 1, x will be zero.

So SumSpecial is returning zero because the last cell in your reference range doesn't have a 1 in it, thus every cell value is being multiplied by zero in your ToSum loop.

What would help is if you posted a workbook with data populated in it, and then with notes in cells that said like "if I typed =SpecialSum("A1:A2", "B1:B10") in this cell I would expect it to equal 6 for reasons x, y, and z". A couple three examples like that would be nice.

[EricJames]

I see, that makes sense. Testing it out with the attached workbook proved that point. A quick test of hiding columns showed that the "sum visible only" portion of the code is working just fine. How do I re-work the "non-sequential" portion so that it checks each cell in ToSum sequentially with its coordinating Reference cell?
I hope I've managed to convey the logic behind my thinking well enough, between my original post and my notes in the workbook.

[walruseggman]

For your Reference Range, you can just select/type in one cell in the correct row if you want, or the whole range; it wouldn't matter.

Please Login or Register  to view this content.

Also, awesome example spreadsheet. If everyone provided examples like this, I would be so happy.

[EricJames]

That seems to have done the trick - thanks so much!

[walruseggman]

Great, please mark this thread SOLVED, and any reputation (the star in the lower left of the posts) is appreciated.

To mark solved, select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.