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 Schiller wrote:
> 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.
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
--
AP
"Craig Schiller" <[email protected]> a écrit dans le message de
news:%[email protected]...
> 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
>
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
Ardus Petus wrote:
> 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
> --
> AP
>
> "Craig Schiller" <[email protected]> a écrit dans le message de
> news:%[email protected]...
>
>>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
>>
>
>
>
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
Ardus Petus wrote:
> 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
> --
> AP
>
> "Craig Schiller" <[email protected]> a écrit dans le message de
> news:%[email protected]...
>
>>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
>>
>
>
>
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
[email protected]
[email protected] with @tiscali.co.uk
"Craig Schiller" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> Ardus Petus wrote:
>
>> 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
>> --
>> AP
>>
>> "Craig Schiller" <[email protected]> a écrit dans le message de
>> news:%[email protected]...
>>
>>>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
>>>
>>
>>
>>
>
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
BruceP wrote:
> 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...)
>
>
Sorry, that does not seem to produce the correct answer. Amy other thoughts?
Sandy Mann wrote:
> 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)
>
>> Hmm. Spoke too soon. Just tried your suggestion, and it appears to um
>> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks