Repeat loop until empty cell

I am looking for an Excel form开发者_开发技巧ula, that can add the results of rows untill it hits an empty cell.

The first row contains a the formula (including value X). The following rows each contain 3 cells (A,B,C) with values in them. It is simple True/False ("=IF(AND(X>=A2;X<=B2);C2;0)) that checks if 'X' > is higher than A, and lower than B, and if true, it returns the value of the C. The formula then looks at the next row and repeats the action, and adds all the returned values to one sum.

ROW1 - A1(formula)
ROW2 - A2(5) - B2(10) - C2(100)
ROW3 - A3(10) - B3(20) - C3(200)
ROW4 - (empty)

ROW2 - If we set X=8, then 8 is > 5, and < 10, and the value 100 is returned.
ROW3 - The action is repeated - this time with the result 0.
ROW4 - The row/cell is empty and the loop stops.
ROW1 - Formula has stopped and shows result (100 + 0 =) 100.

---

This is exactly why they created the SUMIFS worksheet function.

For the whole column (if there's nothing after the first blank row):

=SUMIFS(C:C, A:A, "<" & X , B:B ,">" & X)   

Note: X is the cell that X is located in.

The following will work if you do have stuff after the first blank row:

=SUMIFS(INDIRECT("C2:C" & MATCH(TRUE,INDEX(ISBLANK(A:A),0,0),0)-1),
        INDIRECT("A2:A" & MATCH(TRUE,INDEX(ISBLANK(A:A),0,0),0)-1),"<" & M1,
        INDIRECT("B2:B" & MATCH(TRUE,INDEX(ISBLANK(A:A),0,0),0)-1),">" & M1)

---

I'm not at a computer (with excel) right now.

I do know there is a select current region (I think it was mapped to Ctrl-/?) You should be able to use that to detect ends of contiguous regions. If you want only a column of that, use Range.Intersect to limit to a column (e.g. "$D:D" for column D).

This will leverage the power of Excel instead of coding your own.

Have a look at named ranges (Insert/Name, F3 to pick a named range in formula editor). IIRC you could have 'dynamic' ranges (like: automatically detect contiguous areas) under a name as well. I'd have to try how that worked to provide more details

---

if you're unsure of how many rows there are going to be then you may be better off writing vba to loop through the cells until it finds an empty one

myValue = 8
searchRow = 2
Total = 0
Do While Cells(searchRow, 1).Value <> ""
  If (myValue > Cells(searchRow, 1).Value And myValue < Cells(searchRow, 2).Value) Then
    Total = Total + Cells(searchRow, 3).Value
  End If

searchRow = searchRow + 1
Loop

Cells(1, 1).Value = Total