Can an Excel formula count until a certain sum is reached?

[dunno in dallas]

I have a two column array where the total row # is dynamic. Column A in the array consists of a date and column B consists of a number. Is there an Excel formula that can return the number of rows (starting at B1) required until the sum of the numbers in column B is say 3?

A B
09/01/05 1
09/02/05 0
09/03/05 0
09/04/05 1
09/05/05 1
09/06/05 1

The correct response here would be "5", as it takes 5 rows until the sum of 3 is reached. Thank you so much. -DID

 

[chead5]

Ok, my formulaic solution requires a helper column. In column C make a running total of coulmn b. Then, put this formula where you want your result.

=MATCH(3,B1:B7,0), where B1:B7 would be replaced by your range.

You said your range is variable so I would suggest assigning a dynamic named range that will acomidate the entire set of numbers in C column.

If you can't use the hleper column let me know, I'll get a VBA solution that will work to.

 

[chead5]

here is the VBA solution....

Sub total()
    Dim last_cell As Integer
    Dim total As Integer
    Dim i As Integer

    last_cell = Range("b65536").End(xlUp).Row

    i = 0
    Do Until total = 3 Or i = last_cell
        i = i + 1
        total = total + Cells(i, 2).Value
    Loop
    If total < 3 Then
        Range("c8").Value = "Never reached desired number."
    Else
        Range("c8").Value = i
    End If

End Sub

 

[Aladin Akyurek]

One way...

Book1
	A	B	C	D	E
1	9/1/2005	1
2	9/2/2005	0		3	5
3	9/3/2005	0
4	9/4/2005	1
5	9/5/2005	1
6	9/6/2005	1
Sheet1

E2:

=MATCH(D2,SUBTOTAL(9,INDIRECT("$B$1"&":"&ADDRESS(ROW($B$1:$B$6),COLUMN($B$1)))),0)

which must be confirmed with control+shift+enter.

 

[dunno in dallas]

Thanks for all 3 responses. Prefer a formula over VBA, and of the two formula responses, AA's solution does not require a "helper" column. Aladin, your formula rocks. Thanks a bunch. -DID

 

[sbdeaver]

This is a great formula, but is there a way to have it return the number of records for not an "exact match". For example if you used the same information above, but you wanted to get to 10.5 minutes, how could you get it to still return 12 (if it is ok to be short) or 13 (if it is ok to go over)?

 

[sanrv1f]

Hello and welcome to the board,

not sure where 10.5, 12 & 13 came from

see if this helps you

Excel Workbook
	A	B
1	A	B
2	01/09/2005	1
3	02/09/2005	0
4	03/09/2005	0
5	04/09/2005	1
6	05/09/2005	1
7	06/09/2005	1
Sheet12

Excel 2003

Excel Workbook
	D	E
6	3.5	6
Sheet12

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.