I am trying to create a formula that will sum based on criteria in one
column, but only a cariable region of ciguious cells. My data look like
this:

col1 col2 col3
1
2
3
4
Sum if for above 4 cells
1
2
3
sum if for above 3 cells


Subtotal will not work because it would include the entire column.
Basically I need any forumla that will tell the forumla to continue to sum up
until it reaches an empty cell.

I am drawing a blank on summing until the empty cell.

Let's assume this is your setup:

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

To get the sums in column B enter this formula in *B2* :

=IF(AND(A1<"",A2=""),A1,"")

Enter this formula in B3 and copy down to B9:

=IF(AND(A3="",A2<""),SUM(A$1:A3)-SUM(B$1:B2),"")

--
Biff
Microsoft Excel MVP


"petedacook" wrote in message
...
I am trying to create a formula that will sum based on criteria in one
column, but only a cariable region of ciguious cells. My data look like
this:

col1 col2 col3
1
2
3
4
Sum if for above 4 cells
1
2
3
sum if for above 3 cells


Subtotal will not work because it would include the entire column.
Basically I need any forumla that will tell the forumla to continue to sum
up
until it reaches an empty cell.

I am drawing a blank on summing until the empty cell.

Thanks Biff, but i am beginning to think there is no way to do this without
adding a function inVBA.

The condition for the sum is in column A, the numbers to sumn are in column
B.

Maybe...
Select the column and press [F5] Special Blanks OK.
Click the Autosum toolbar button [Alt+=].

(Choose Edit Insert Shift Cells Right to shift across,
you can add extra cells to the selection by Ctrl+Clicking..)

"petedacook" wrote:

I am trying to create a formula that will sum based on criteria in one
column, but only a cariable region of ciguious cells. My data look like
this:

col1 col2 col3
1
2
3
4
Sum if for above 4 cells
1
2
3
sum if for above 3 cells


Subtotal will not work because it would include the entire column.
Basically I need any forumla that will tell the forumla to continue to sum up
until it reaches an empty cell.

I am drawing a blank on summing until the empty cell.