Totaling varying cell ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a way to total a range of cells that changes often. All cells are in the same column. For example: There are numbers in cells A7 through A10, I need the total in cell B10 (that one is easy). Skip cell A11 and a new set of numbers are in cells A12 through cell A25, total is in cell B25 (this is easy as well). The problem I am looking to solve it that tomorrow (hypothectically speaking) there are numbers in cells A1 through A15 and the total is in B15. Cell A16 is blank with numbers in A17 and A18 with that total in B18. The totals in the "B" column should only reflect the totals of each "Section" (as it is known around here). All the "B" numbers are totaled elsewhere.
 
George,

The solution for this requires that your numbers not start in A1, but
in A2.

In cell B2, use the formula:
=IF(A3="",SUM($A$2:A2)-SUM($B1:B$2),"")
and copy down as far as your data may exist.

If you might have more than one blank cell in succession, use this
formula in cell B2 instead:
=IF(AND(A2<>"",A3=""),SUM($A$2:A2)-SUM($B1:B$2),"")

HTH,
Bernie
MS Excel MVP

George said:
I need a way to total a range of cells that changes often. All
Click to expand...
cells are in the same column. For example: There are numbers in
cells A7 through A10, I need the total in cell B10 (that one is easy).
Skip cell A11 and a new set of numbers are in cells A12 through cell
A25, total is in cell B25 (this is easy as well). The problem I am
looking to solve it that tomorrow (hypothectically speaking) there are
numbers in cells A1 through A15 and the total is in B15. Cell A16 is
blank with numbers in A17 and A18 with that total in B18. The totals
in the "B" column should only reflect the totals of each "Section" (as
it is known around here). All the "B" numbers are totaled elsewhere.
 
Hi George
try the following formula in B10:
=SUM(OFFSET($A10,0,0,-(ROW()-MAX(IF($A$1:$A10="",ROW($A$1:$A10),0)))))
enter this as aray formula (CTRL+SHIFT+ENTER)

you can just copy this formula to your other section ends in column B.
the cell reference will adapt automatically.

HTH
Frank
 
I have tried both of these methods and I believe that I might have left something out in my original posting. I guess what I am trying to do is to add a column of numbers between blank rows. Something like this (Today's Sheet)

Column A Column B Column C Column D Column
Row1 1
Row2 1
Row3 2
Row4 45 (This is the total of A1:A3
Row5 2
Row6 2
Row7 1
Row8 1
Row9
Row10 75 (This is the total of A5:A9

And this is Tomorrow's Shee

Column A Column B Column C Column D Column
Row1 1
Row2 1
Row3 2
Row4 1
Row5 1
Row6 70 (This is the total of A1:A5
Row7 1
Row8 1
Row9
Row10 1
Row11 45 (This is the total of A7:A10

All numbers are always in the same column. Only the rows differ from day to day. I really need something in place that will accommodate this daily variation

Thanks.
 
Hi George
o.k. your first post suggested that you wnated the totals in row 3 and
10 respectively (according to your example). So just enter the
following in B4
=SUM(OFFSET($A3,0,0,-(ROW()-MAX(IF($A$1:$A3="",ROW($A$1:$A3),0)))))
as array formula. Just copy this to B10 and it should work.

Frank
 
Hi George
first there was a small typo in the original formula. should read:
=SUM(OFFSET($A3,0,0,-(ROW()-1-MAX(IF($A$1:$A3="",ROW($A$1:$A3),0)))))
This would be the formula for row 4. Just enter it is B4 annd hit
CTRL+SHIFT+ENTER. Now you can copy it to all other blank rows.

If you want this automatically happen, try the following:
1. Add a helper column (lets say column C) and enter the following
formula as array formula in C2:
=IF(A2="",MAX(IF($A$1:$A1="",ROW($A$1:$A1),0)),"")
2. in B2 enter the following 8normal entered - no array formula):
=IF(A2="";SUM(OFFSET($A1,0,0,-(ROW()-1-C2))),"")
copy both formulas down

somehow I was not able to combine the check A2="" and the array formula
in one -> use of a helper column

HTH
Frank
 
Back
Top