Add changing last rows

  • Thread starter Thread starter Craig Schiller
  • Start date Start date
C

Craig Schiller

Hi gurus -

Okay, in my spreadsheet I have monthly subtotals in say, (this month),
cells B67 and B70. Next month, as I add data, the subtotals will be in
cells B74 and B77. In another cell, I would like a formula that lets me
add these two figures automagically --- right now I have to revise the
formula in the totals cell manually because I'm too stoopid to figure
out how to specify the changing monthly cells. Any help would be
appreciated. TIA.

Craig
 
Craig said:
Okay, in my spreadsheet I have monthly subtotals in say, (this month),
cells B67 and B70. Next month, as I add data, the subtotals will be in
cells B74 and B77. In another cell, I would like a formula that lets me
add these two figures automagically --- right now I have to revise the
formula in the totals cell manually because I'm too stoopid to figure
out how to specify the changing monthly cells. Any help would be
appreciated. TIA.
Click to expand...

You could put each month's totals in a particular
column, then just sum the entire column. As sum(A:A) sums
the entire column A. Then all you have to worry about is
keeping stuff out of the column that you don't want summed.

-Or-

Look up INDIRECT and OFFSET. These will allow you to specify
where a cell gets its data by specifying locations. You could then
do something like (from mem, coding at the terminal, probably
loads of typos in this)

=sum(offset(a1,d1,1):offset(a1,d2,1))

to indicate where to sum some data, with the rows to start and
end indicated in the cell d1. Look up the syntax for offset to
see if this actually makes sense.

Then you need to have something that calculates where the new
data is. It's pretty difficult to figure out how to do that from what
you gave here. But, supposing you have a VBA macro that does
the insertion each month, then you need to add some steps to
it to place the row numbers for these values in cells someplace.
Socks
 
If your monthly subtotals are in B46, B53, B60, B67, B74, etc, you can get
the grand total with following formula:
=SUMPRODUCT(--(MOD(ROW(B46:B200)-ROW(B$46),7)=0),B46:B200)

HTH
 
Ardus -

Thank you very much! I'll give that a go.

Now, let's say my subtotals aren't in every seventh row but sometimes in
every seventh, sometimes in every sixth, etc. Is there a more generic
solution that I could use in every circumstance?

TIA,
Craig
 
Hmm. Spoke too soon. Just tried your suggestion, and it appears to sum
every seventh row. That's not what I need. What I need, I guess, is a
formula that merely sums the last row in the column, whatever that row
is, and the last row-3 in that same column. Sorry for any confusion.

TIA,
Craig
 
If the second cell to be added is always three cells up from the last cell
and if there are no holes in the data then try:

=INDEX(B:B,COUNT(B:B))+INDEX(B:B,COUNT(B:B)-3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
You could also name your monthly subtotal cells, then refer to those
names in your totalling formula. For instance, you could name your
subtotal cell for January, "JanSubT", February could be, "FebSubT",
etc. Your totalling formula, while a bit clunky, would work every
time:

=SUM(JanSubT,FebSubT,MarSubT...)
 
Yes, I could do that, but it's as much work as inputting the cell
numbers manually. I'm looking for something more automatic. Thanks for
the thought, though.

Craig
 
Back
Top