Formula to Identify groups of numbers to be added.

[jeff]

Hoping I can get a cell formula that will calculate as follows:

Cells in Column H have this relative formula in them.
=IF(M12="Yes", "",C12)
This results in a cell will either show a number, or is blank.

Each week or so, another entry is made to the next empty line.
After awhile, Column H might look something like this: Number are in
cells H12 – H14
There is a blank at H15. Then numbers in cells H16-H19, and a blank at
H20
Column H
Line12 13.89
Line 13 7.89
Line 14 6.14
Line 15 Blank cell (this shows up as blank cell)
Line 16 9.54
Line 17 8.99
Line 18 12.88
Line 19 11.74
Line 20 Blank cell (this shows up as blank cell)

I would like for the consecutive numbers to be added together. This
formula would be located in all cells in column I. In this example,
the formula will look at column H, and Add 13.89+7.89+6.14. That’s
all because the next line is blank.
It would also add 9.54+8.99+12.88+11.74

I would like for these totals to show up in the cell next to each
blank cell.
Example: the 1st blank line is in cell H15. I want the total of cells
H12-H14 to be in I15. The next blank is in cell
H20. So, I20 would have the sum of cells H16-H19. Again, the formula
would be in every cell in Column I.

The amount of lines to be totaled will vary. This column will extend
down into the hundreds.

If anyone can help, I’d appreciate it. I hope this has been enough
information
Thanks,
jeff

 

[Bernie Deitrick]

Jeff,

In cell I2, enter the formula

=IF(H3="",SUM($H$1:H2)-SUM($I$1:I1),"")

and copy down to match the formulas in column H.

HTH,
Bernie
MS Excel MVP


Hoping I can get a cell formula that will calculate as follows:

Cells in Column H have this relative formula in them.
=IF(M12="Yes", "",C12)
This results in a cell will either show a number, or is blank.

Each week or so, another entry is made to the next empty line.
After awhile, Column H might look something like this: Number are in
cells H12 – H14
There is a blank at H15. Then numbers in cells H16-H19, and a blank at
H20
Column H
Line12 13.89
Line 13 7.89
Line 14 6.14
Line 15 Blank cell (this shows up as blank cell)
Line 16 9.54
Line 17 8.99
Line 18 12.88
Line 19 11.74
Line 20 Blank cell (this shows up as blank cell)

I would like for the consecutive numbers to be added together. This
formula would be located in all cells in column I. In this example,
the formula will look at column H, and Add 13.89+7.89+6.14. That’s
all because the next line is blank.
It would also add 9.54+8.99+12.88+11.74

I would like for these totals to show up in the cell next to each
blank cell.
Example: the 1st blank line is in cell H15. I want the total of cells
H12-H14 to be in I15. The next blank is in cell
H20. So, I20 would have the sum of cells H16-H19. Again, the formula
would be in every cell in Column I.

The amount of lines to be totaled will vary. This column will extend
down into the hundreds.

If anyone can help, I’d appreciate it. I hope this has been enough
information
Thanks,
jeff

 

[jeff]

Jeff,

In cell I2, enter the formula

=IF(H3="",SUM($H$1:H2)-SUM($I$1:I1),"")

and copy down to match the formulas in column H.

HTH,
Bernie
MS Excel MVP


Hoping I can get a cell formula that will calculate as follows:

Cells in Column H have this relative formula in them.
=IF(M12="Yes", "",C12)
This results in a cell will either show a number, or is blank.

Each week or so, another entry is made to the next empty line.
After awhile, Column H might look something like this: Number are in
cells H12 – H14
There is a blank at H15. Then numbers in cells H16-H19, and a blank at
H20
                      Column H
Line12  13.89
Line 13 7.89
Line 14 6.14
Line 15 Blank cell (this shows up as blank cell)
Line 16 9.54
Line 17 8.99
Line 18 12.88
Line 19 11.74
Line 20 Blank cell  (this shows up as blank cell)

I would like for the consecutive numbers to be added together. This
formula would be located in all cells in column I. In this example,
the formula will look at column H, and Add 13.89+7.89+6.14.   That’s
all because the next line is blank.
It would also add 9.54+8.99+12.88+11.74

I would like for these totals to show up in the cell next to each
blank cell.
Example:  the 1st blank line is in cell H15. I want the total of cells
H12-H14 to be in I15. The next blank is in cell
H20. So, I20 would have the sum of cells H16-H19. Again, the formula
would be in every cell in Column I.

The amount of lines to be totaled will vary. This column will extend
down into the hundreds.

If anyone can help, I’d appreciate it. I hope this has been enough
information
Thanks,
jeff

I appreciate your help. I'm confused. In your formula, you have H2 and
H3. The 1st line of data starts at H12. I'm sure this will make a
difference where I put the formula. I would tweak it myself to correct
the cell references, but I'm not really sure what it's suppose to do.
Please retype your response, given that the data starts on line 12.
Thanks again for your help.
jeff

 

[jeff]

I appreciate your help. I'm confused. In your formula, you have H2 and
H3. The 1st line of data starts at H12. I'm sure this will make a
difference where I put the formula. I would tweak it myself to correct
the cell references, but I'm not really sure what it's suppose to do.
Please retype your response, given that the data starts on line 12.
Thanks again for your help.
jeff- Hide quoted text -

- Show quoted text -

Well, I screwed up, and instead of deleting my response, I deleted the
response I got from the person who gave me to solution.
Who ever you are, I really appreciate it. I thought it was not
correct, but I changed a couple things, and it works great!!
Thanks very much.
jeff

 

[jeff]

Jeff,

In cell I2, enter the formula

=IF(H3="",SUM($H$1:H2)-SUM($I$1:I1),"")

and copy down to match the formulas in column H.

HTH,
Bernie
MS Excel MVP


Hoping I can get a cell formula that will calculate as follows:

Cells in Column H have this relative formula in them.
=IF(M12="Yes", "",C12)
This results in a cell will either show a number, or is blank.

Each week or so, another entry is made to the next empty line.
After awhile, Column H might look something like this: Number are in
cells H12 – H14
There is a blank at H15. Then numbers in cells H16-H19, and a blank at
H20
                      Column H
Line12  13.89
Line 13 7.89
Line 14 6.14
Line 15 Blank cell (this shows up as blank cell)
Line 16 9.54
Line 17 8.99
Line 18 12.88
Line 19 11.74
Line 20 Blank cell  (this shows up as blank cell)

I would like for the consecutive numbers to be added together. This
formula would be located in all cells in column I. In this example,
the formula will look at column H, and Add 13.89+7.89+6.14.   That’s
all because the next line is blank.
It would also add 9.54+8.99+12.88+11.74

I would like for these totals to show up in the cell next to each
blank cell.
Example:  the 1st blank line is in cell H15. I want the total of cells
H12-H14 to be in I15. The next blank is in cell
H20. So, I20 would have the sum of cells H16-H19. Again, the formula
would be in every cell in Column I.

The amount of lines to be totaled will vary. This column will extend
down into the hundreds.

If anyone can help, I’d appreciate it. I hope this has been enough
information
Thanks,
jeff

Thanks a lot! This works perfect.
jeff