Folks, I am stymied. I posted last week about having to count travel sectors in a particular set of journeys so that I could sum the number of people on a train at a given station, not just those getting on or off. To do this, I had set up a matrix adjacent to my ticket data. the matrix consisted of a line for each ticket purchased, and then a number of columns (one for each station). For each station that the passenger passed through, a 1 was listed under the respective column with that stations name. At the end of the journey, the number of stations can be summed across the page. So, if a passenger passes through 10 stations, there would be a 1 in the ten columns adjacent to the particular ticket number, and the row would sum to 10.
Imagine this for several thousand passengers. While its easy to count the number of tickets sold, I need to be able to count the number of people aboard the train at each station. THis could be done by summing each station column.
To complicate matters, in my data set, there are 8 trains, either inbound or outbound, and up to 30 stations.
I have tried several simple examples off the net with similar constructs (2 vertical, 1 horizontal criteria) and can get them to work using sumproduct and sumifs. However, changing the ranges in these examples so that they point to my data gives errors.
I haven't got access to excel jeanie at work so will try and post an example when I get home. Essentially, Column A has TRain Names, Column B holds the direction, columns C to AF hold the station data. Column headers are in Row 1.
Here are the two attempts which worked with example data sets.
Utilisation!$B$2 = contains the train Name
Utilisation!$C$2 = contains the train direction
=SUM(IF('2016'!$a$1:$a$306862=Utilisation!$B$2,IF('2016'!$b$1:$b$306862=Utilisation!$C$2,IF('2016'!$c$1:$af$1=Utilisation!$J$30,'2016'!$c$2:$Af$306862))))
this is entered as an array formula. unfortunately, does not give the right answer with my data
and
$B$2 = contains the train Name
$C$2 = contains the train direction
=SUMPRODUCT(('2016'!$a$2:$a$306861=$b$2)*('2016'!$b$2:$b$306861=$c$2)*('2016'!$c$1:$az$1=Utilisation!$J$30)*'2016'!$c$2:$Az$306861)
This one worked with the sample data but not with mine.
Couple of questions: does the data set and criteria have to be on the same tab?
Should each cell reference be identified with sheet name eg '2016'!$a$2:$a$306861=$b$2) SHOULD BE ('2016'!$a$2:$a$306861=Utilisation!$b$2) even when the calculation is on the same page as the criteria?
Pause for now while I head home.
Imagine this for several thousand passengers. While its easy to count the number of tickets sold, I need to be able to count the number of people aboard the train at each station. THis could be done by summing each station column.
To complicate matters, in my data set, there are 8 trains, either inbound or outbound, and up to 30 stations.
I have tried several simple examples off the net with similar constructs (2 vertical, 1 horizontal criteria) and can get them to work using sumproduct and sumifs. However, changing the ranges in these examples so that they point to my data gives errors.
I haven't got access to excel jeanie at work so will try and post an example when I get home. Essentially, Column A has TRain Names, Column B holds the direction, columns C to AF hold the station data. Column headers are in Row 1.
Here are the two attempts which worked with example data sets.
Utilisation!$B$2 = contains the train Name
Utilisation!$C$2 = contains the train direction
=SUM(IF('2016'!$a$1:$a$306862=Utilisation!$B$2,IF('2016'!$b$1:$b$306862=Utilisation!$C$2,IF('2016'!$c$1:$af$1=Utilisation!$J$30,'2016'!$c$2:$Af$306862))))
this is entered as an array formula. unfortunately, does not give the right answer with my data
and
$B$2 = contains the train Name
$C$2 = contains the train direction
=SUMPRODUCT(('2016'!$a$2:$a$306861=$b$2)*('2016'!$b$2:$b$306861=$c$2)*('2016'!$c$1:$az$1=Utilisation!$J$30)*'2016'!$c$2:$Az$306861)
This one worked with the sample data but not with mine.
Couple of questions: does the data set and criteria have to be on the same tab?
Should each cell reference be identified with sheet name eg '2016'!$a$2:$a$306861=$b$2) SHOULD BE ('2016'!$a$2:$a$306861=Utilisation!$b$2) even when the calculation is on the same page as the criteria?
Pause for now while I head home.