Counts with multiple criteria, COUNTIFS not working

Hello,

I am having difficulty creating a count matrix based off of multiple criteria. One column contains a from code (TTT_from), one column contain as to code (to_TTT), one column contains if its an origin, and one contains if its a destination. I am trying to count how many instances of:

from code+origin|to code + destination

are in my data set. I have attach a file containing a sample of the data, the matrix I am trying to create, and the COUNTIFS formula I wrote which does not work (box 95|33 highlighted in the matrix).

I'm sure it is something small I'm missing because its Friday, but any help would be greatly appreciated.

count_help.xlsx

Thanks for your reply.

My issue is I need a count of 1 in the matrix if (in this example) E2 is 95 and V2 is ORIGIN, and G32 is 33 and W32 is DESTINATION; this condition is filled in the sample. Is it possible or am I going to have to manipulate the data so it is formatted:

| 95 | 33 | ORIGIN | DESTINATION | all on one row.

I have hundreds of thousands of data points so if it is possible in the current format that would be fantastic.

I'm not sure I understand your overall problem. The cell where you have the formula is for an origin of 95 and destination of 33. So I get that those are the two numbers you are interested in. However, in your data, I think you need to count how many times there is an origin of 95 and a destination of 33 in the same trip (not in the same row, which is what your existing formula does). Is that correct? Your sample data only shows one trip so it hides the complexity of your true set of data of hundreds of thousands of row. Is TripID a unique identifier for a trip?

Correct. The sample I attached is only one trip, and the TripID as well as the Origin and Destination are established values based on a number of criteria. Each instance you see between Origin and Destination are merely data points, and what constitutes a trip is a variety of factors. Based on what I attached, I am looking for a 1 in the 95 | 33 box in the origin/destination matrix because what the data is showing is a single trip, starting at 95 and ending at 33.

I have attached a larger sample of the data.

count_help_large.xlsx

I was hoping someone would provide a solution using formulas, because I'm not sure how I would do that. However, I can look at a VBA solution if you are open to macros.

Is the data for each trip always contiguous? That is, is it always in a solid block starting with Origin and ending with Destination?

I have written a macro to do this. The macro part isn't hard but I am not sure about how to interpret your data. How can you tell what is the origin and destination numbers for a given trip? I thought maybe the TTT_from code on the line containing ORIGIN was the origin, and the to_TTT code on the line containing DESTINATION was the destination. But there doesn't seem to be any rhyme nor reason as to how those numbers change on the intermediate lines throughout the same trip. For example, on trip 100069_41899.1756944444, the TTT codes are changing constantly throughout the trip. On many lines they are different and on some lines they are the same.

For another example, trip 100069_41905.6722222222 seems to have 33 as both the origin and destination, which doesn't make sense to me.

I either need a rigorous set of rules for how to treat these, or an explanation for what it all really means so I can derive my own rules.

Here is the macro as it stands now.

The codes are numbers assigned to arbitrary regions in the area that we are evaluating, and each row is a GPS ping. 'truckid' is a unique ID number for the truck's GPS so I used that and the 'readdate_from' to create a unique trip ID. What I have tried to do with this data is count the number of trips in the dataset based on the criteria of a trip being if a truck is stopped for longer than 45 minutes and less than 4 hours, which I am still working on as how it is set up now does not do exactly that. When this criteria is filled I want to grab the code from the start of the trip and the code from the end of the trip, and then populate the matrix. Any help with this would be greatly appreciates as well.

The reason the codes seem to change sporadically between origin and destination is due to trucks moving through these regions during a trip. Additionally, the reason sometimes the codes don't change between origin and destination is because the truck has what we define as a trip and does not leave the region they started in.

Does this help?

In that case I think the macro does what you need--have you tried it? If time permits I'll look at the other issue that you just raised about when the new trip starts.

Yes I tried it and it does exactly what I need, thank you very much.

If you find time to take a look at the other issue that would be fantastic. I think I'm getting close, but I'm having some difficulty.