how to get multiple VLOOKUP results to be a SUM on a different page

[blakestandal]

Hello and thank you whomever is taking time to read this post. I really appreciate it.

I have been tasked with helping my boss get a comprehensive excel doc together that saves a lot of time.

In doing this, I have made enormous progress, but i've hit a snag.

In a perfect world, I want to have a "cost breakdown" sheet that auto-populates the $ values according to what "category" each cost is under. All on a separate sheet.

attached are links to pictures with my predicament.

https://drive.google.com/a/energyst...0B58yjs0XislhbFE0TDJVVFgwbHc/view?usp=sharing

This shows my data table (without being filled in a whole lot). on the left you see a category, it's been set from a list of about 6 total categories, but could end up being more...

On the right, you see the subtotal price that is generated from these mock products.

I want to have multiples of the same category- more specifically, their $ value, be SUM-ed and placed on the next link's page.

https://drive.google.com/a/energyst...0B58yjs0XislhS29OTENqVmtlSkE/view?usp=sharing

you'll notice that I can get ONE value to populate in the cell. (solar, for instance works because its only looking for ONE value on the cost breakdown sheet, but I need it to auto-sum ALL corresponding values).


Please post to this thread if you have questions about my process or need more information to get a result!

 

[BQardi]

Or how about an even more simple SUMIF formula?

=SUMIF(Column to sum,Column to look for lookup value,lookup value)

=SUMIF(Sheet1!$F:$F,Sheet1!$A:$A,"Solar")

Replace "Solar" with the lookup value (your dropdown)

 

[BQardi]

VLOOKUP will stop at the first found value...

 

[blakestandal]

Or how about an even more simple SUMIF formula?

=SUMIF(Column to sum,Column to look for lookup value,lookup value)

=SUMIF(Sheet1!$F:$F,Sheet1!$A:$A,"Solar")

Replace "Solar" with the lookup value (your dropdown)

Any benefit of doing it this way?

 

[blakestandal]

VLOOKUP will stop at the first found value...

Perhaps I'm just clueless to some simple fact... but there's no way it could work with more than one? like a series of VLOOKUP formulas? Just curious because I want to learn while I work on this.

 

[BQardi]

You can do more advanced lookups with SUMPRODUCT. I guess you can say it's more versatile.
SUMIF (and SUMIFS) should be faster as far as I know...

 

[blakestandal]

You can do more advanced lookups with SUMPRODUCT. I guess you can say it's more versatile.
SUMIF (and SUMIFS) should be faster as far as I know...

I appreciate the info!

Thankfully, my data table is one sheet's worth... and the MASTER list of all products we sell/deal with might be a couple pages... point being; we probably don't have a speed-solving issue to worry about

 

[BQardi]

Then you can just use what you prefer
I prefer SUMIF because I think it is easier to read (and faster to write), but I guess it's just a matter of taste...
Of course when I need more advanced lookups, I use SUMPRODUCT...
Oh, and to your other question:

Perhaps I'm just clueless to some simple fact... but there's no way it could work with more than one? like a series of VLOOKUP formulas? Just curious because I want to learn while I work on this.

The simple answer; no!

 

[oldbrewer]

lookups require data to be in ascending or descending order - and - you have many solars - so you would in effect have to lookup every solar separately...

it is just n ot really suitable for this task....