Sum Rows, Draw Line one certain value met

mrhaboobi

New Member
Joined
Jan 31, 2019
Messages
2
Hi Team

Im trying to do something fairly simple but failing :)

Lets say i have 10 rows

each row has a value

1
2
10
20
24
56

and so on.

In one column i have a Cumulative count

1
3
13
33

and so on.

Based on the cumulative count i want to be able to draw a line under a row

If ( cumulativeValue > 10) then draw line under row. ( or Draw line under the previous row. )

thats the first simple formatting thing i would like to do.

Id also like it so that the cumulative count can reset itset itself so that the count start from the row which had a line under it

eg

1
3
13
-------
20

In short im trying to create a sheet that can Chunk data up ( rows ) based on the sum of a value in one of the cell. Everytime i reach a threshold i want to draw a line, and reset the count back.

Any thoughts?

---
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,313
Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.
 

mrhaboobi

New Member
Joined
Jan 31, 2019
Messages
2
Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.
Super hero :)

Does exactly what i want, ive changed the forumla to point to a cell to deteermine the threshold value. The question i have new to improve it one little bit more, is the first column where it says Sprint.

SprintStory PointsCumulative
133
158
14.512.5
1315.5
15.521
1324
2327
2330
2333
2841

<colgroup><col><col><col></colgroup><tbody>
</tbody>


essentially id like that value to be set to 1 or 2 or 3, but all the row within the group would have the same value.

eg in the example above with a Threshold of 20 the line would be ruled under the row with value 21, and the first column would all say 1. the next block would be 2 and so on per block. Is there way to increment those numbers the same way as you do along with the cumulative value?

Lastly whats a good resource to learn these "more advanced" technique?

Thanks
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,313
The "Sprint" formula should just be a variation of the original formula, something like:

=IF(OR(SUM(B1)>=10,ROW()=2),SUM(C1,1),C1)

Assuming you want this in column C, put this in C2 and copy down.


As far as finding Excel resources, there is a vast wealth of options, it just depends on your time, needs, budget, and style of learning. Books, videos, sample problems, trainers, and more. A longtime member here has compiled an incredible list of web resources which you can find here:

https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1084627-best-vba-book.html

Scroll down until you see the list. The list is a bit daunting! :eek: But don't get overwhelmed, nobody knows everything about Excel. Just look for something that looks interesting, or useful for something you're working on, work on it until you get it, then try something else. If you want a more structured approach, you can get a book (the Dummy's books are usually pretty good), or there are a series of YouTube videos you can follow. There are links to those in the list too.

Good luck!
 

bluesfanmtv

New Member
Joined
Jan 23, 2020
Messages
2
Office Version
2016
Platform
Windows
Welcome to the Board.

Let's assume you have headers in row 1, and your values are in column A. Put this formula in B2:

=IF(SUM(B1)>=10,A2,SUM(B1,A2))

Drag down the column as needed. Now select columns A:B, click Conditional Formatting > New Rule > Use a formula > and enter:

=$B1>=10

Click Format... > Border > click the solid line from Style, and the Underline from the Border box, then click OK.
THANK YOU FOR THIS!!! I have found this formula to be a springboard to what I am looking to achieve. Rather than an underline, I have set the conditional format to highlight the cell after reaching 500, now my question:
I am tracking hours on call and I need a reset at 500, but I also need the residual number to carry over into the next 500 hours total...so let's say my "Hours" total 519.5, can I carryover the remaining 19.5 in my next 500 count?
Hours on callTotal hours on call
13​
13​
13.5​
26.5​
24​
50.5​
64​
114.5​
112​
226.5​
200​
426.5​
51​
477.5​
21​
498.5​
21​
519.5​
0​
0​
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,313
Welcome to the Board!

Yes, that's possible. Consider:

Book1
ABC
1Hours on callTotal hours on callTotal hours on call (version 2)
2131313
313.526.526.5
42450.550.5
564114.5114.5
6112226.5226.5
7200426.5426.5
851477.5477.5
921498.5498.5
1021500519.5
111029.529.5
122554.554.5
131771.571.5
14200271.5271.5
15199470.5470.5
1656500526.5
1777.5104104
18104104
Sheet10
Cell Formulas
RangeFormula
B2:B18B2=IF(B1=500,MOD(SUM($A$1:$A1),500)+A2,MIN(500,SUM(B1,A2)))
C2:C18C2=MOD(N(C1),500)+A2


The B formula will cap the total at 500, then continue with the overage on the next line. The C formula will have the value exceeding 500, but continues on the next line with the overage. So essentially, the rows will all be the same except where the totals exceeds 500. Your preference on how to treat that. The C formula is a lot simpler.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,979
Messages
5,411,636
Members
403,383
Latest member
Excelacity

This Week's Hot Topics

Top