Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells between blank cells
I have numbers in cells I2:I20, cell I21 is blank and then more numbers in
cells I22:I30 and I31 is blank. This will repeat down the sheet. I would like a formula in column J to sum all the numbers in between the blank cells. In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells I22:I30, etc. I know I can go to those cells and enter the sum equation but is there something I can copy down the sheet? Let me know if you don't understand and I will try to rephrase the question. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells between blank cells
Sub AddFormulas()
j = Cells(Rows.Count, "I").End(xlUp).Row + 1 k = 2 For i = 1 To j If Cells(i, "I").Value = "" Then Cells(i, "J").Formula = "=SUM(I" & k & ":I" & i - 1 & ")" k = i + 1 End If Next End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To use the macro from the normal Excel window: 1. ALT-F8 2. Select the macro 3. Touch Run To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200856 "Jambruins" wrote: I have numbers in cells I2:I20, cell I21 is blank and then more numbers in cells I22:I30 and I31 is blank. This will repeat down the sheet. I would like a formula in column J to sum all the numbers in between the blank cells. In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells I22:I30, etc. I know I can go to those cells and enter the sum equation but is there something I can copy down the sheet? Let me know if you don't understand and I will try to rephrase the question. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells between blank cells
Enter in J2 and fill down...
=IF(ISBLANK(I2),SUM($I$2:I2)-SUM($J$1:OFFSET($J$1,ROW()-2,0)),"") -- Jim Cone Portland, Oregon USA "Jambruins" wrote in message I have numbers in cells I2:I20, cell I21 is blank and then more numbers in cells I22:I30 and I31 is blank. This will repeat down the sheet. I would like a formula in column J to sum all the numbers in between the blank cells. In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells I22:I30, etc. I know I can go to those cells and enter the sum equation but is there something I can copy down the sheet? Let me know if you don't understand and I will try to rephrase the question. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells between blank cells
thank you
"Jim Cone" wrote: Enter in J2 and fill down... =IF(ISBLANK(I2),SUM($I$2:I2)-SUM($J$1:OFFSET($J$1,ROW()-2,0)),"") -- Jim Cone Portland, Oregon USA "Jambruins" wrote in message I have numbers in cells I2:I20, cell I21 is blank and then more numbers in cells I22:I30 and I31 is blank. This will repeat down the sheet. I would like a formula in column J to sum all the numbers in between the blank cells. In cell J20 I would like it to sum cells I2:I20, in cell J30 sum cells I22:I30, etc. I know I can go to those cells and enter the sum equation but is there something I can copy down the sheet? Let me know if you don't understand and I will try to rephrase the question. Thanks. |
#5
|
|||
|
|||
Quote:
thx... Snowiii33 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
Range naming cells with blank cells through coding | Excel Discussion (Misc queries) | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Excel inserted 0's in cells linked to blank cells | Excel Discussion (Misc queries) | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) |