Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post

    Number of journeys on a particular day

    Hi,

    Firstly, I apologise, I'm sure this has a simple solution I'm not seeing (or finding on-line).

    I need to work out how many journeys are planned every calendar month. Unfortunately the timetable is weekly and seasonal.

    I just need the first step to get me started on monthly summaries:

    In Sheet2, I have the timetable for this year, showing the route, day of week, and dates that the timetable operates From/To.

    In Sheet 1, I have a date per row. I want to take the date say 8th July 2015, look in Sheet 2 and find the number of journeys planned on this day.

    So, for the first route (Yell), it would look up the date on Sheet 2, select the row where the date was between the to/fom dates, and the day of the week matched and maybe INDEX?? column 4 to get the value 50.

    Any help would be appreciated, even if you just say "do this as a SUMIFS ARRAY formula". At least that way I'll know where to focus on.

    Thanks in advance!

    Jim (Shetland Isles)
    Attached Files Attached Files
    Last edited by jmacleod; 2015-07-08 at 05:11. Reason: forgot attachment

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Solved it myself, thanks to those of you who looked.

    The solution was the following as an array formula:

    =SUM((Sheet2!$D$2:$D$22)*(--(Sheet2!$C$2:$C$22=C2))*(Sheet2!$A$2:$A$22<=A2)*(S heet2!$B$2:$B$22>=A2))

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..the day format in column [C] on [Sheet1] must match the day format in column [C] on [Sheet2]

    If you use this slightly adjusted array formula in cell [D2] on [Sheet1], you could then copy it across to column [K], and then down the required rows.
    =SUM((Sheet2!D$2:D$22)*(--(Sheet2!$C$2:$C$22=$C2))*(Sheet2!$A$2:$A$22<=$A2)* (Sheet2!$B$2:$B$22>=$A2))

    zeddy
    •Tomato Source Supplier
    Last edited by zeddy; 2015-07-08 at 12:56.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    jmacleod,

    As an alternative, with this vba approach no formulas are needed. Click the journeys button and your grid will be filled according to the table on sheet 2. The totals column will be totaled with the sum of the day's journies.

    HTH,
    Maud

    Journeys.png

    In a standard module:
    Code:
    Sub Journeys()
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range, cell As Range
    Dim datepair As Range, dte As Range
    Set rng = Worksheets("Sheet1").Range("A2:A366")
    Set datepair = Worksheets("Sheet2").Range("A2:A22")
    '----------------------------
    'GET JOURNEY DATA
    For Each cell In rng
        For Each dte In datepair
            If cell >= dte And cell <= dte.Offset(0, 1) And _
            InStr(1, dte.Offset(0, 2), cell.Offset(0, 2), 1) Then
                For I = 3 To 10
                    cell.Offset(0, I) = dte.Offset(0, I)
                Next I
            End If
        Next dte
        cell.Offset(0, 11) = WorksheetFunction.Sum(Range(cell.Offset(0, 3), _
                             cell.Offset(0, 10)))
    Next cell
    End Sub
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •