Sum cells in a column and display in next column and repeat

[nileshrg]

Hi,
I am trying to sum the rows in a column and if the sum exceeds 2400, then put the total in the cell in next column prior to it exceeding 2400.

Once that is done, it repeats the same from the next row onward.

I have attached the workbook with Before and After sheets. Hope I am able to explain myself.

Thanks.

[Steffen Thomsen]

Hi,

This code will do

Please Login or Register  to view this content.

[ffffloyd]

I have added a sheet to your workbook called Calculation. It does what you ask. See the added headings and their accompanying comments for an explanation. In brief:

• column K keeps a running total;
• column L keeps track of what the next number is going to be;
• column M works out if there is even going to be a next number; and
• column N says, "If the running total plus the next number exceeds the nominated maximum OR if there isn't going to be a next number then publish the total so far."

You could actually put that logic in column B but, to keep all the complexities away from the main display, column B just says that if there is a number in column N then I'll show it here.

[abousetta]

Maybe try this non-vba approach. In Cell B2 add this formula and drag down to the end

Please Login or Register  to view this content.

Hope this helps.

abousetta

[nileshrg]

Hi Steffen,

I tried your code. It gives me the total after it has crossed 2400. I need to calculate the total just before it goes to 2400.

[Steffen Thomsen]

This will do

Please Login or Register  to view this content.

[nileshrg]

Thanks Steffen. It works better but still it does not check for the last few cells. In the same example it does not show the total for last 3 values.

[Marcol]

A variation on ffffloyds' method for finding the next value, and using SUMIF()

Change the value in E1 to vary the results (Use the grouping button to hide the helper Columns)

[Steffen Thomsen]

That because it doesnt reach the cap, try this code

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[nileshrg]

Hi Steffen,
This works like a charm. i realized something weird. Pls correct me.
When I use this code in a sample sheet it works fine but when i use it as a part of my entire code it recognizes variables "Total" and "cell" are not defined.

Why so?

[Steffen Thomsen]

Thats because you have option explicit at the top.

Add this at the top of the code

Please Login or Register  to view this content.

[snb]

@Steffen

Please Login or Register  to view this content.

is an overcomplicated way of writing:

Please Login or Register  to view this content.

But if you use specialcells(2), this test is redundant.

[Steffen Thomsen]

Thank you snb

[nileshrg]

Thanks a ton all of you for providing both vba and non-vba solutions.