sumifs/sumproduct with 2 criteria in columns and 1 criteria horizontal

[ajm]

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.

 

[AliGW]

Can you please provide a link to the original thread?

 

[ajm]

HI again Ali. Its the one you had a look at last week.

https://www.mrexcel.com/forum/excel-questions/999425-count-passengers.html

I actually solved it while I was cycling home last night. (and just for the record, I say NO! to lycra for men!!)

i used:

=SUMIFS(INDEX('2016'!$U$4:$AX$306860,0,MATCH(Utilisation!$J30,'2016'!$U$4:$AX$4,0)),'2016'!$D$4:$D$306860,Utilisation!$B$1,'2016'!$R$4:$R$306860,Utilisation!$C$1)

where
'2016'!$U$4:$AX$306860 contains the 1's for each station passed through by each passenger (this is what my earlier post worked out). Row 4 is a header row containing the station numbers

Utilisation!$J30 = the station number
'2016'!$D$4:$D$306860 = the train names as per the individual tickets
Utilisation!$B$1 = the train Name (reference cell on tab where I am building my graph data sets)
'2016'!$R$4:$R$306860 = the direction of the train as per the individual tickets
Utilisation!$C$1 = the train direction (reference cell on tab where I am building my graph data sets)

so, I am thinking my error originally had something to do with specifying only one column as the sum range but am not entirely sure.

 

[AliGW]

Thanks for the link.

Its the one you had a look at last week.

I remembered it, but I look at so many threads for people that it becomes difficult to go back and track individual ones down, even after a few days.

Thanks for letting us know your solution.

 

[ajm]

I look at so many threads for people

Ali, I am constantly amazed at people's generosity with their time in helping others on this site. Keep it up, the collective effort is very much appreciated by all.