# Thread: Number of journeys on a particular day

1. ## 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.

Jim (Shetland Isles)

2. 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. ..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

4. 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