Results 1 to 4 of 4

20150708, 05:09 #1
 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 online).
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)Last edited by jmacleod; 20150708 at 05:11. Reason: forgot attachment

20150708, 06:57 #2
 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))

20150708, 12:38 #3
 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 SupplierLast edited by zeddy; 20150708 at 12:56.

20150708, 21:39 #4
 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