+ Reply to Thread
Results 1 to 8 of 8

sum based on criteria

  1. #1
    Registered User
    Join Date
    04-03-2010
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question sum based on criteria

    I use multiple workbooks and each work book contains many worksheets with names of customers which contains their order. I would like to know if it is possible to create formula to calculate a product if any customer should order it.

    For instance, in the workbook "April", if customer A order item ASB099 and so does customer B, G, H, K, L (all different worksheet in the workbook). Can someone help me write a formula to add up the quantity of ASB099 within the entire workbook.

    To provide more details of each worksheet (customer order). Every worksheet follows the same format in that column B in the worksheet contains the product ID, column I: quantity, column J: weight of the product.

    Just from what I can come up in my head, I would like to do something like this: If ASB099 exists on the A column on any worksheet, sum up all of it's corresponding weight in the J column.

    I hope someone can help. Thanks for your time.
    nichiaichi

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: sum based on criteria

    If possible you would be much better off holding all your data on one worksheet. This would allow you to perform your analysis in a much easier way.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: sum based on criteria

    Nichiaichi,

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    http://www.mrexcel.com/forum/showthread.php?t=459495

  4. #4
    Registered User
    Join Date
    04-03-2010
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: sum based on criteria

    Quote Originally Posted by Paul View Post
    Nichiaichi,

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    http://www.mrexcel.com/forum/showthread.php?t=459495

    ++Thanks Paul and Dom. I so new and clueless I don't even know where to put this response. I do respect others and certainly not do cross posting again. I guess everyone has questions they want to resolve as quickly as possible after all. The reason for the rush is that I suspect my employees skimming from the top and need to find a solution to keep track of individual items going in and out (inventory) based on weight of products. Since I am already here and willing obey by the rules, I wonder if I can make amends and get some help. Business in this environment is very tough which is the reason we need more control over inventory-- something we should have done a long time ago.
    Thanks again!

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: sum based on criteria

    Quote Originally Posted by Domski View Post
    If possible you would be much better off holding all your data on one worksheet. This would allow you to perform your analysis in a much easier way.

    Dom
    No problem. My original point still stands. You would make your life much easier for yourself if you kept all your data on one sheet and added a column to specify which customer each entry was for.

    Edit: You can then easily use Pivot Tables or conditional formula such as COUNTIF, SUMIF, SUMIFS etc to track volumes etc

    Dom
    Last edited by Domski; 04-04-2010 at 11:30 AM.

  6. #6
    Registered User
    Join Date
    04-03-2010
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: sum based on criteria

    that's the thing. Each sheet contains unique customer orders which we already use to calculate cost, weight quantity and everything else on that sheet. However, 5 other customers may order the same product within that month which is what I would like to calculate automatically if possible. The custom can order any item she wants from a list of 2000 products in 10 different categories. What I would like to do is add up the total quantity and weight of a specified product within the entire workbook (monthly). For example, item ASB009 is ordered numerous times in March by several customers, is it possible to calculate ASB009 if it occurs within the workbook?

  7. #7
    Registered User
    Join Date
    04-03-2010
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: sum based on criteria

    If you can help, I can upload a sample workbook for you to review.

  8. #8
    Registered User
    Join Date
    04-03-2010
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: sum based on criteria

    If you are willing to help, I have uploaded a sample copy of a workbook I am working with to this page:
    http://asia-charisma.net/client-serv...RDER_04APR.xls

    Each customer has his/her own sheet and unique order. I would like to add a seperate sheet keeping a tally of what is ordered throughout the month. Can I have separate sheet containing a list of all my products and provide it a formula to tally up all quantity of that product within the worksheet?

    Thanks for you help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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