Sum of Cell references between sheets if certain text is in adjacent cells

[krowell]

If the text in cell E22 of sheet2 equals *insert state abbreviation here*, then add contents of cell Z22 of sheet2 to the Sum in the cell of the corresponding state.

I have read several posts about this type of operation but I am having a tough time trying to use the bits and pieces I have learned for my purposes.

Attached is the actual workbook I am trying to apply it to.
I tried to illustrate how sheet2 will look when filled out in sheet3.

Fuel Tax Rental Guide&trip-sample.xlsx

The hope is that I will only have to type mileages into sheet2, print it up, and be done.

This sheet is protected to some extent. It's how I received it and my predecessor is no longer here to ask them the password.
I don't know but don't believe it prevents me from making the aforementioned changes.

Thank you in advance for your time.

[FDibbins]

Hi krowell and welcome to the forum

Before I even start trying to answer this (and Im sure every-one will agree with this) please please plzzzzz try and avoid the use of merged cells at all costs, they can create havoc with creating formulas and functions!!

With that said, back to look at your request lol

[krowell]

I did not know that, thank you for pointing out my newbery.
With that sheet I tried to replicate what we get sent by corporate.
We can make a more useful sheet from a formula stand point but without approval the information had to look the same. please inform me how best to blend the two as affectivity.
could I add that sheet and have it be populated by the sheet that does the 'math' so I still have what I need to send to the boss?

[FDibbins]

I understand completely about "hand-downs" from corporate lol, and if necessary, we can take the approach you suggested

[FDibbins]

I was looking at your file again. Could you please add some examples of your expected outcome, as well as how you arrived at that

[krowell]

Sheet3 is an example of what the filled in trip report sheet will look like.
All I want to type in is the date, state, ending/beginning miles, and ending meter reading.

As in sheet3 there may be several entries for each state. From there I would like the rental tax calculations sheet (sheet1) to be filled in from the data on the trip report (sheet2).

I.e. for New Mexico, the cell E36 in the Miles Trav column on sheet1 would sum the miles (from the non-toll miles column in sheet2) for all entries with NM in the state column (of sheet2). So in this case, add the 300 miles from 31-JAN entry, to 300 from 3-FEB, to 200 from 3-FEB, to get a total of 800 miles. Have that number be what the calculations on sheet1 are based on.

Do the same opporation for the other states, and a similar opperation for the 'Fuel Pur' column of sheet1, then print and be done with it.

Currently, my admin spends more time on this than I think it should take and I want to streamline this process as much as possible.

Again, thanks for the help. Let me know if i need to provide any other information.

[krowell]

Here's what i came up with for the column 'miles trav' on sheet1:

=SUMIF(Sheet2!E22:G45, "state_abbreviation", Sheet2!Z22:AD45)

It seems to be doing the trick but I don't know if it will 'break' if someone tampers with it.

Is there a better way to go about this?