How to sum multiple columns that change sizes?

Originally Posted by snb

How to help without a sample workbook ?

Thanks for the quick response. The problem is that I'm behind a firewall at work and cannot upload any files. I will take the file home with me and upload it after work.

The sample spreadsheet is attached. Hopefully this will help.

I'm still struggling to find a solution to this problem. Not only have I searched here on this forum, but elsewhere on the web. The best I can do is in the code below. However, there is an issue with summing up the second set of numbers as the code loops back up to cell F5 and the issue is compounded as it moves onto the other sets of numbers.

I'm sure there are some smart people out there that can solve this easily and without using old counter logic.

Also it would be useful to have a cell at the bottom that provides a Grand Total of all the sums of the columns this code is adding up.

Please Login or Register  to view this content.

try it

Please Login or Register  to view this content.

Thank you very much nilem. This code worked perfectly. I just wish that I understood it. Over time I will learn.

I ran the code in the example spreadsheet I uploaded and it worked great. However, when I ran the macro in the actual report, it did not work. Since I do not understand the code shared, I cannot say where the issue is and can only guess it is because each of the departments in reality contains more than 10 inventory items each. The format in the example is identical to the actual sheet except that there are many more rows than in the example I uploaded.

So my question now is how can this code be modified to take into account that the row count will often change from month to month as items are either added to inventory or removed?

Please Login or Register  to view this content.

The formula above appears to have relative reference to the cells and will only sum a column consisting of just 10 cells.

two versions

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks Nilem, you are amazing!!! I will use the second option and hopefully I will one day understand it all.

I have attached a sample spreadsheet along with my Macro Workbook with steps I take in this complete process. A lot of the code was recorded macros to begin with and the others were a combination of code people here on this forum helped with and from watching videos on Lynda.com.

If anyone wants to look through this mess and provide any tips; that would be great, but can survive with what I have so far.

Thanks again!