Count multiple items multiple times in Excel

Question

I'm trying to produce a bar graph of the number of vehicles stopping each minute over a one hour period. I need a table of mins (1 to 60) vs. number of vehicles to do this.

However, my source data is in the form:

Vehicle Arr. Min    Dep. Min    Duration
A       10          12             2
B       19          19             0
C       31          32             1    
D       41          44             3
E       42          42             0
F       54          55             1

So in the example above: Vehicle A needs to be counted under minutes 10, 11 and 12.

Vehicles D and E both need counting for minute 42

I can use COUNTIF or COUNTIFS to count a single instance, but I can't figure out a way to count a vehicle across several minutes.

I'm not worried about identifying individual vehicles.

Hope this makes sense.

Answer 1

Add 60 columns to the right titled 1 ... 60 and have a formula in the rows below them (columes E and further) of the sort:

=IF(AND($B2<=E$1;$C2>=E$1);1;0)

Then sum up for each minute column and you have your count. I have to say CAPSLOCKs answer works in the newer Excel versions. I recommend using his approach for the neater way you are looking for!

Answer 2

Use the countifs function.

Say you were checking for how many are stopped for minute 3 then you could use

=COUNTIFS(A1:A7, "<=3", B1:B7, ">=3")

and change A1:A7 with whichever range your Arr. Min is in and B1:B7 with whichever range your