# Count Until Sum of Counted = Value

#### hockey390

##### Board Regular
What I'm trying to accomplish is counting how many numbers from column A (starting at A2 and going down) it takes until it adds up to the number in B1. Twist of difficulty is I want a fraction.. So this would result in 3.5. 1+4+3+(half of 2). If A5 was = 3, answer would be 3.3.

 A B 1 9 2 1 3 4 4 3 5 2 6 7 7 8 8 5 9 4

<tbody>
</tbody>

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### NeonRedSharpie

##### Well-known Member
Code:
``````Function ADDUNTIL(rng As Range, value As Variant) As Variant

Dim count As Long

For Each cell In rng
count = count + cell.value
Select Case count
Case value
Exit Function
Case Is < value
Case Is > value
Exit Function
End Select
Next cell

End Function``````

Here is a user defined function that will do what you want.

#### Domenic

##### MrExcel MVP
Using native functions, maybe...

Code:
``=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))<=B1))+LOOKUP(0,(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9)-B1,(B1-(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9))/A2:A9)``

Actually, in case B1 is greater than or equal the sum of values in A2:A9, try...

Code:
``=IF(B1>SUM(A2:A9),"N/A",IF(B1=SUM(A2:A9),COUNT(A2:A9),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))<=B1))+LOOKUP(0,(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9)-B1,(B1-(SUBTOTAL(9,OFFSET(A2:A9,,,ROW(A2:A9)-ROW(A2)+1))-A2:A9))/A2:A9)))``

Hope this helps!

Replies
8
Views
278
Replies
2
Views
276
Replies
3
Views
483
Replies
3
Views
552
Replies
8
Views
298

1,191,190
Messages
5,985,201
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back