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

mbsamuel6

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.

Paul Newcome

Have you tried this yet?

=SUMIFS({Amount}, {Garage}, Garage@row, {Requesting Garage}, [Requesting Garage]@row)

Paul Newcome

Have you tried this yet?

=SUMIFS({Amount}, {Garage}, Garage@row, {Requesting Garage}, [Requesting Garage]@row)

mbsamuel6

Perfect. That's exactly what I was trying to do. I knew there was a more easier way to do it.

Looks like I was working backwards. I was referencing "Garage@row" first before referencing the other sheet.

Thank you