+ Reply to Thread
Results 1 to 6 of 6

Find total # of each item sold and average cost of item across multiple worksheets

  1. #1
    Registered User
    Join Date
    03-01-2017
    Location
    Tucson, Arizona
    MS-Off Ver
    Starter Edition
    Posts
    4

    Find total # of each item sold and average cost of item across multiple worksheets

    Hello everyone,
    I have been searching the forum and google for a possible answer to my problem, but have not been able to find a solution that matches what I need. I have a workbook that contains all of my purchase orders for last year. Each purchase order is contained on it's own worksheet. I have created a worksheet called 'Item Sales 2016', and I want to find formulas that will search each PO worksheet for each item number, find the qty of that item sold on each worksheet, and then sum those numbers and return the total onto the 'Item Sales 2016' sheet. I then want it to also search each worksheet for the item number and find the "landed cost" for each item on each PO and return the average cost for that item. Each PO sheet is set up in exactly the same way.

    So for example, for my first Item #, I want the formula to search each PO worksheet and have it add up the numbers in the "Qty" column and have that total returned on the "Total Sold" column of the 'Item Sales 2016' sheet. Then I want the total in the "Landed Cost Each" column for that item to be averaged out across all PO's and returned on the "Average Landed Cost" column of the 'Item Sales 2016' sheet.

    I hope this is clear enough, and please feel free to ask for further clarification.

    Thank you in advance for your help.
    Attached Files Attached Files
    Last edited by Nariadnaia; 03-01-2017 at 04:33 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Find total # of each item sold and average cost of item across multiple worksheets

    Hi welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Registered User
    Join Date
    03-01-2017
    Location
    Tucson, Arizona
    MS-Off Ver
    Starter Edition
    Posts
    4

    Re: Find total # of each item sold and average cost of item across multiple worksheets

    Thank you FDibbins, I have edited my post accordingly. :-)

  4. #4
    Registered User
    Join Date
    03-01-2017
    Location
    Tucson, Arizona
    MS-Off Ver
    Starter Edition
    Posts
    4

    Re: Find total # of each item sold and average cost of item across multiple worksheets

    Here is the sample of my workbook.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Find total # of each item sold and average cost of item across multiple worksheets

    I have added a UDF to pull out your sheet names (I use that in a name range for the calcs), I also created a new sheet (called dates) to hold the sheet names. Add any new sheets before the Dates sheet.
    Then I used SUMPRODUCT() to pull in the values you wanted.

    Take a look and let me know if that was what you wanted?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-01-2017
    Location
    Tucson, Arizona
    MS-Off Ver
    Starter Edition
    Posts
    4

    Re: Find total # of each item sold and average cost of item across multiple worksheets

    That looks like it would work. Unfortunately, I have the excel starter edition and it doesn't "support running macros and other active content", so I'm guessing I might be stuck with having to manually do the calculations.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto decrease quantity of an item when the item is sold.
    By acidust in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2016, 07:46 PM
  2. [SOLVED] Item Cost + eBay Fee % = NOT Item Sale Price, so what is?
    By CatSqueezer in forum Excel General
    Replies: 8
    Last Post: 05-31-2015, 03:32 AM
  3. Automatically Input Cost of Item Based on Item Name?
    By boba7523 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-05-2014, 01:44 AM
  4. How to calculate cost of item sold at a mark-up of 2.5%?
    By yjohari in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2013, 07:38 PM
  5. Cost of good sold (formula to work out how much to sell item for?)
    By hemeravisuals in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2013, 09:51 PM
  6. Calculating total cost from range of cell tally's by cost of item.
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-04-2012, 10:27 AM
  7. Formula For Total Cost of Increasing Item Cost
    By dwax in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-05-2009, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1