Crazy Formula or Macro

[HotRod]

I'm wondering if it is possible to do the following with a formula or if I
need to create a macro. I'd prefer to stay away from the macro if possible.


I Have a sheet that has all of my business trips on it (Sorted by Date) I
want to sum the sheet so that I get the total mileage for each week.

1) Since some weeks I travel more than others how can I determine if the
mileage belongs to week 1 or 2, for ex. One week I may have five business
trips C5:C10 and then week two, two business trips C11:C12, but the
following month it could be reversed. So How can I get another cell to SUM
the first week?


2) Is there a way to sort this out in a formula?

This is what I think I need to figure out

a) Set start date
b) Determine date ranges for week's 1 - 4
c)

 

[Arvi Laanemets]

Hi

At start you have to define a week. The WEEKNUM function in Excel applies to
US week system only, where even 1-day week is possible.

After you have decided about week numbering system, you have to find a
formula to calculate a week number from trip date, and enter it into
additional column.

When for every date in your table is an according week number calculated (I
myself prefer to use it in format "yyyy.ww"), then you can use SUMIF
function to calculate mileage for specific week. Or you create a pivot
table, where mileages are summed weekly. Both solutions don't need your
table to be sorted at all.

 

[Tom Ogilvy]

do you have a date associated with each entry? If so (assume column A
below) you can just use two sumif functions

=sumif(A:A,">=StartofWeek",C:C)-Sumif(A:A,">EndofWeek",C:C)

 

[Guest]

Hi,
This assumes the date is in Column C and that there is a header. Start on
the first date, which would be C2, if there is a header. The miles are
assumed to be in Column D. It also assumes you want to total Monday through
Sunday.
Sub Macro1()
StartDateWeekday = Weekday(ActiveCell.Value)
StartAddress = ActiveCell.Address
StartRow = ActiveCell.Row
Do Until ActiveCell.Value = ""
If StartDateWeekday = 1 Then
ActiveCell.Rows("2:2").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & (ActiveCell.Row - StartRow) &
"]C:R[-1]C)"
ActiveCell.Offset(1, -1).Select
StartRow = ActiveCell.Row
StartDateWeekday = Weekday(ActiveCell.Value)
Else
ActiveCell.Offset(1, 0).Select
StartDateWeekday = Weekday(ActiveCell.Value)
End If
Loop
End Sub

Hope this helps.
Thanks,

 

[HotRod]

Tom
Where would I place this "SumIF"? at the bottom of the column or in a
new row for each entry? Not sure I understand what it's meant to do?

 

[Tom Ogilvy]

Its meant to:

I want to sum the sheet so that I get the total mileage for each week.

You can put it anywhere as long as the formula isn't in the column that is
being summed. If it is, then you would have to change C:C to a specific
range such as C1:C200 and then put it below row 200.

If checks if the date in column A is greater than or equal to the start date
of the week in question. (you change the condition to something like

">=May 29, 2005"

If so, it adds the number of miles for that date

so you add up all miles that occured after or on May 31 2005

then using as similar formula, you subtract the sum of all the miles driven
after the end of the week

">Jun 4, 2005"

the difference is the number of miles you drove the week of May 29, 2005 to
Jun 4, 2004.