How to make EXCEL sort, analyze and sum up values

Hello everyone.

I need a help with my project. I want to create a spreadsheet to calculate IFTA miles per state for a small trucking company.

Here is data I'm working with:
* Multiple trips for each truck with different route all the time. So sometimes trip route takes 3-4 states, sometimes it is 10 states and more. (See attached file "Trip report State miles.pdf") Trip report State miles.pdf
* I usually get a trip report that has states driver traveled, and miles traveled in each state.
* Each trip may have repetitive states with different mileage value on different dates, it can not be summed up on reports.

What I want Excel to do:

* to analyze data from different trips Trips miles per state.xlsx and sum up miles per each state. As a result I want to have table that will show me how many miles has been traveled in each state.

Please give me your advice how would you build a spreadsheet.

Thank you!

Welcome to the forum

see the attached file
manage to arrange data in a single table and use pivot table

That's what I was looking for.

Great job, I may look dumb but I have to understand how you did it.

I usually have about 50-100 reports to work with. Would your way to deal with it be good for so many trips?

I don't understand how you make this tableMiddle table.PNG

Really appreciate your help!

I Just copied and and paste the data in the table and used pivot table