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

blakestandal

New Member
Joined
Sep 20, 2016
Messages
8
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/energystarsconstruction.com/file/d/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/energystarsconstruction.com/file/d/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!
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
I need permission to open doc

far better to make a small pretend spreadsheet, paste it into the reply box and tell us what you want
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
cat1cat1322.5
cat2cat2649.5
cat3cat3654
cat4cat4548.75
cat5cat5884
cat6cat6556.25
cat2cat17.5
cat3cat28.25
cat4cat39
cat5cat49.75
cat6cat510.5
cat1cat611.25
cat2
cat3
cat4
cat5
cat6
cat2is this anything like your scenario ?
cat3
cat4
cat5
cat6
cat1
cat2
cat3
cat4
cat5
cat6
cat2
cat3
cat5
cat5
cat5

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>
 

blakestandal

New Member
Joined
Sep 20, 2016
Messages
8
https://drive.google.com/open?id=0B58yjs0XislhODFYUVV5R1p6cHc

Here is the raw Doc.

sorry for not having the settings correct to view. I appreciate people's responses!


While looking at the doc. Look at sheet Cost Breakdown. I want the Category column AND Sub Totals column to be added so that costs associated with each of the 3 primary charges (seen on Proposal sheet) will give the sum of their monetary values ON the Proposal sheet.

so as to understand the thought= person tallies the costs of materials in ANY random order and their totals always get added to the final sheet.

goal= to save time from re-entering data.

this is just version 1... ultimately I'm working on getting ALL our products into one master catalog so as a word/item search will autopopulate in the cost breakdown sheet AND help track an inventory excel to help us manage our OH's and what each job needs.

let me know if this helps you guys! I know i'm close to solving the formula, but college seemed to have missed these fancy little tricks.

ps, "Proposal" sheet U19 gives the goal i want... U15 shows what another youtube video recommended, I just expanded on the idea but it didn't seem to like it....
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
i HAVE LOOKED AT YOUR your post 4 plus the google drive but I still cannot see what you want

please make up a sheet with colored widgets or whatever and show us what you want

once we understand the question we will sort it for you
 

blakestandal

New Member
Joined
Sep 20, 2016
Messages
8
  1. CategoryItem #Descriptioneach PriceQuantitySub Total
    Solarxx1300w200102000
    HVACxx2AC unit100011000
    Solarxx3Inverter200102000

    <tbody>
    </tbody>



Here is a basic version of my sheet....

only, I have the "category" cell a drop-down box of 6 options. so, if I have any multiples of any category, I want the total price to be SUM-ed in a different cell.

IE, on the different sheet. "Total HVAC= 1000, Total Solar= 4000"...etc.

but it's imperative that I have some form of LOOKUP formula because the exact cell and quantity of cells associated with each category differ job to job.



ps, the link opens on the Google doc from any computer I use (not signed in either) not sure why no one is able to open...


thanks!
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
CategoryItem #Descriptioneach PriceQuantitySub Totaldropdown =solar
Solarxx1300w200102000
HVACxx2AC unit100011000
Solarxx3Inverter200102000total price =20830
cat3xx4descr1504200
cat4xx5descr2758600
cat5xx6descr399121188
cat6xx7descr4145162320
Solarxx8descr5775385
HVACxx9descr68810880
Solarxx10descr799151485
cat3xx11descr8110202200
cat4xx12descr9121253025
cat5xx13descr10132303960
cat6xx14descr11143355005is this what you want
Solarxx15descr12154406160
HVACxx16descr13165457425
Solarxx17descr14176508800
cat3xx18descr151875510285
cat4xx19descr161986011880
cat5xx20descr172096513585
cat6xx21descr182207015400

<colgroup><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
it is a very simple sumproduct formula

=sumproduct(($a$2:$a$22=$h$1)*($f$2:$f$22))
 

Forum statistics

Threads
1,082,573
Messages
5,366,378
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top