At 05:49 20/07/2008 -0600, John Meyer wrote:
i have a spreadsheet set up with the miles that I've biked on a
particular day. What I want is to the side to get a grouping of
those entries by day and the sum. I can do it with subtotals but
that inlines them with the data where i want the totals to the side.
The data looks like this, btw:
Date Miles
To work 07/18/08 3.2
To home 07/18/08 3.2
To work 07/19/08 3.2
To home 07/19/08 3.2
Here's one solution. Let's imagine that the dates are in column B,
starting at row 2, with the distances starting in C2. We can use the
SUMIF() function to total all the values for a particular date. Enter in D2:
=SUMIF(B$2:B$99;B2;C$2:C$99)
Here the entire range B2 to B99 is examined for values that are equal
to B2 - the date for the current row - and the corresponding values
from column C are totalled. Fill this formula down column D to
produce a column of daily totals.
The only problem left is that the daily total will be shown for every
row, whereas you probably want to see only one statement of it per
day. The key fact about the last row for each day, of course, is
that the date on the following row is different, so we need the total
to appear only where this is the case. Change the formula in D2 to:
=IF(B2<>B3;SUMIF(B$2:B$99;B2;C$2:C$99);"")
and fill this down column D. Now only where the data in the
following row is different from the current row's date do we see the
subtotal; otherwise the cell displays nothing.
But I think perhaps you instead want a separate list of dates and
totals, one per row - which is easier. In this case, put the first
date in (say) E2 and fill down column E - to create a separate list
of dates. Then enter in F2:
=SUMIF(B$2:B$99;E2;C$2:C$99)
and fill this down column F. For each date in column E, column F
will show the total of the corresponding distances.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
