Screen Shot 2016-03-09 at 00.46.04.png
I need to find a formula that will give me a total in D2.
The range that I will be using will be in B in the below picture.
Screen Shot 2016-03-09 at 00.43.15.png
Upon the sheet in the first pic, I will select the product I wish to find a total for down column A.
Therefore I need to find a way to only add up the numbers in column B (2nd pic) to match the same product in from C (2nd pic).
All help really appreciated!
put the following formula in cell D2:
You'll have to change Sheet2!$B$2:$B$18 by your actual product range
=SUMIF(Sheet2!$B$2:$B$18,Sheet1!$A2,Sheet2!$A$2:$A$18)
You'll also have to change Sheet2!$A$2:$A$18 by your actual units ordered range.
Pierre Leclerc
_______________________________________________________
If you like the help you got,
Click on the STAR "Add reputation" icon at the bottom.
Thanks for the reply, but I don't quite follow..
Basically I want an overview sheet (Stock Report) that will let me select products and tell me the number from Order sheet.. What steps to I take?
D2 in Stock Report then put in =sumif then select which part in the order sheet?
You put the Sumif formula in D2. You then copy this formula down the column in your Stock report sheet.
Make sure you have in column A, the list of products you want to check.
The formula will give you the number of products ordered from you Order sheet.
That is why I told you to change both Sheet2 section to suit your actual sheet name.
CODE]=SUMIF(Sheet2!$B$2:$B$18,Sheet1!$A2,Sheet2!$A$2:$A$18)[/CODE]
probably it is just your sheet names.
rename your units ordered sheet to "sheet2" then put the formula in.
hmm OK looks like P24 beat me to it, but I'd also note the sheet1 probably needs changing or removing too.=SUMIF(Sheet2!$B$2:$B$18,$A2,Sheet2!$A$2:$A$18)
Does it matter if the product I will be selecting will be from a drop down menu? This really has to be key for what I need and want.
My first sheet will be a 'base sheet' - I've listed all my products and all information that I need in that.
Every other product input in my other sheets - monthly orders, monthly sales, monthly stock reports - is comprised from drop down lists that are referenced from whatever goes into the 'base' sheet. All information that's derived through manual input i.e sales or orders is derived from VLOOKUP forumlas..
So when I want a monthly stock report, I select Product X from a list, and a formula in relevant cells (Sales and Purchases) adds up all the figures in orders and sales that are relevant to the product I choose to select.
There is never going to be a set determined product that I will be asking figures for.
From what I can see it's something like a VLOOKUP and SUMIF function needed, but I haven't the excel knowledge to begin to get to what I need beyond explaining best as to what I need.
Sorry for being such a newb. I can pick things up quite easily, and then understand them, but probably skipping various levels of needed excel understanding at this point.
Again, many thanks..
It would be a lot easier if you could attach a sample workbook with your actual set up.
And no, the value you are searching coming from a drop down menu has no effect on the formula.
Here is a simple sample workbook..
https://mega.nz/#!V8xRyLrJ!Xa63seV_J...vTQ67bJ6VbS41M
Thanks
Last edited by t.richards; 03-09-2016 at 06:10 PM.
See the formulas in your Stock report sheet.
I was not sure in which column to put the sales formula (in Sold or in Sales).
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(note: this change is not optional)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks