Attempting to get sum of values from two sheets. Sumifs w/ Vlookup?


This question has been asked in a number of ways before.

I have two sheets. All I am trying to do is get the total amount of pieces of equipment based on different criteria.

Here is the first sheet:

The first sheet is showing Requesting Garage as in the garage making the request for the equipment and "Garage" as in the garage where the equipment is currently stored. It works as a booking request sheet.

Below is the second sheet. That has the amount for the different scenarios depending on where the equipment is being transported to when booked. Those numbers below are static for now but can change in the future. All I want to do is is use that second sheet as a lookup sheet for the first sheet.

Example: on the first row of the first sheet we have "Requesting Garage" is Tiffin. For "Equipment" we have Skylift Patriot and the current Garage is "Lima".

Basically Tiffin is requesting equipment from Lima. We want to the amount for that round trip based on the second sheet. I would have another column on the first sheet called "Transport Amount" to show this.

Best way to do this?

I will like to say I did find a way to do this. I created a formula with a bunch of IF AND statements saying something like:

IF(AND([Requesting Garage]@row = "Bucyrus", Garage@row = "Van Wert"), {Amount1}, IF(AND(Garage@row = "Bucyrus", [Requesting Garage]@row = "Lima"), {Amount2}, IF(AND(Garage@row = "Bucyrus", [Requesting Garage]@row = "Findlay"), {Amount 3}, ........))))))))))

That does work, but I was wondering if there was an easier way to do this using lookups or @cell or something.


Best Answer