Sumif based within non blank cells

[petedacook]

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.

 

[T. Valko]

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),"")

 

[petedacook]

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.


I am beginning to think this guy just needs to change the layout of his
data, because his spreadsheet kind of sucks.

Pete K

 

[Lori Miller]

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..)