Add up to a specified number

barrai

New Member
Joined
Nov 25, 2015
Messages
3
hi
I have a list of customers purchases in a1:a20. I have one number as a basis for example in D1 (d1=500)
How to calculate sum of row a1 until which row is 500.

 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Timbo50

New Member
Joined
May 15, 2015
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
You say calculate sum of row A1, which are characters not a numbers? Also do you wish to calculate values under the value of D1 or do you want to calculate values up to row 500?
 

barrai

New Member
Joined
Nov 25, 2015
Messages
3
i want calculate sum of numbers from a1 to any row when total equal or grater than d1.
for example in up image ,B2+B3+B4+B5 is our answer.
 

Syed Ali Raza

New Member
Joined
Aug 2, 2018
Messages
1
Hey barrai,
You need to create a helper column in column C which shows the running total of column B and then use the below function.

=IFERROR(SUMPRODUCT(OFFSET(B1,1,0,MATCH(E1,C2:C21,1)+1)),C2)

Hope this help.

C:\Users\User\Desktop\Query

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
i want calculate sum of numbers from a1 to any row when total equal or grater than d1.
for example in up image ,B2+B3+B4+B5 is our answer.

Hi,

Not sure what you want as a result at All, you need to be more specific.

Take your pick.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Customer</td><td style=";">Purchase</td><td style="text-align: right;;"></td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">a</td><td style="text-align: right;;">221</td><td style="text-align: right;;">221</td><td style=";">b</td><td style=";">Customer b in Column A when Sum reaches 500+</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">a</td><td style="text-align: right;;">150</td><td style="text-align: right;;">371</td><td style="text-align: right;;">4</td><td style=";">Row 4 in Column B (Not including Header row) when Sum reaches 500+, meaning 340</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">a</td><td style="text-align: right;;">95</td><td style="text-align: right;;">466</td><td style=";">$A$5</td><td style=";">Cell address of Customer when Sum reaches 500+</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">b</td><td style="text-align: right;;">340</td><td style="text-align: right;;">806</td><td style=";">$B$5</td><td style=";">Cell address of Purchase when Sum reaches 500+</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">c</td><td style="text-align: right;;">439</td><td style="text-align: right;;">1245</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">c</td><td style="text-align: right;;">233</td><td style="text-align: right;;">1478</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">c</td><td style="text-align: right;;">208</td><td style="text-align: right;;">1686</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">c</td><td style="text-align: right;;">240</td><td style="text-align: right;;">1926</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">c</td><td style="text-align: right;;">263</td><td style="text-align: right;;">2189</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">d</td><td style="text-align: right;;">299</td><td style="text-align: right;;">2488</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet165</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUM(<font color="Blue">B$2:B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=INDEX(<font color="Blue">A2:A11,MATCH(<font color="Red">D1,C2:C11,1</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=MATCH(<font color="Blue">D1,C2:C11,1</font>)+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=CELL(<font color="Blue">"address",INDEX(<font color="Red">A2:A11,MATCH(<font color="Green">D1,C2:C11,1</font>)+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=CELL(<font color="Blue">"address",INDEX(<font color="Red">B2:B11,MATCH(<font color="Green">D1,C2:C11,1</font>)+1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

C2 formula copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top