+ Reply to Thread
Results 1 to 8 of 8

Inventory Excel Question?

  1. #1
    Registered User
    Join Date
    01-12-2008
    Posts
    5

    Inventory Excel Question?

    I am trying to put a inventroy sheet togeather for the bar I work for. The computer will spit out reports of every item sold. I am trying to get excel to add them up and display them nice and simple. The probem is the reports only list what is sold so the items will not be on the same line every time.


    What I am trying to do is have excel count the number in the cell NEXT to a cell with name x. For example:

    A B
    1 Bay Breeze 2
    2 Quervo Tequilla 5
    3 Captain Morgan 3
    4 Margarita w/ quervo 3
    5 Malibu 9

    So what I want to see from the above info is:

    Malibu 11 (bay breese + malibu)
    Quervo 8 (quervo + margarita)
    Captain 3


    So next week if no bay breeze was sold and some martinis were sold the cells would be different

    A B
    1 Quervo Tequilla 5
    2 Captain Morgan 3
    3 Martini 5
    4 Margarita w/ quervo 3
    5 Malibu 9



    So in one cell I can say look in a for the name Quervo or Magrarita or Margarita call Add all the numbers in cell B and show me them

    Look the names Malibu or baybreese andshow me the total of the cells next (b) to the name.


    If that makes any sence to anyone I could really use some help!

    Thanks!
    Ryan
    Last edited by ryanc; 02-25-2009 at 01:10 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inventory Excel Question?

    SUMIF will total the cells with a matching criteria in a nearby column. You can string several SUMIFs together to what you want.

    =SUMIF($A$1:$A$5,"Bay Breeze",$B$1:$B$5)+SUMIF($A$1:$A$5,"Malibu",$B$1:$B$5)
    =SUMIF($A$1:$A$5,"Quervo Tequilla",$B$1:$B$5)+SUMIF($A$1:$A$5,"Margarita*",$B$1:$B$5)
    =SUMIF($A$1:$A$5,"Captain*",$B$1:$B$5)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Re: Inventory Excel Question?

    I'd go more with something like the attached sheet, but I'm making the assumption that there are hundreds of types of liquor, and you want to be able to search for any key word at any time, and know what the results you're getting are based on.

    NOTE:
    I didn't put anything in here to recognize duplicates, but you will see all results posted.
    Last edited by mewingkitty; 03-13-2009 at 06:45 PM.
    =IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)

  4. #4
    Registered User
    Join Date
    01-12-2008
    Posts
    5

    Re: Inventory Excel Question?

    AWESOME I can;t wait to get to work and try both of these! Thanks a million for the QUICK responses! :D

  5. #5
    Registered User
    Join Date
    01-12-2008
    Posts
    5

    Re: Inventory Excel Question?

    Thanks A million. UI ended up combining and using BOTH! It works PERFECT!

    I have attached the file incase anyone else would like to take a look.

    Thanks Again
    Attached Files Attached Files

  6. #6
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Re: Inventory Excel Question?

    Hey right on, always glad to help.

    Now as a simple form of payment, I'll take one bottle of each. 8)

    j/k j/k

    Thanks for posting your final example afterwards, it'll help others to finalize their projects if they find this link as a result of a search!

    mew!

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inventory Excel Question?

    you can simplify that formula in K8 looking for drinks with Captain in it like so:

    =SUMIF($A$1:$A$350,"*Captain*",$B$1:$B$350)

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inventory Excel Question?

    And if you shorten it up just a little more, you get the Capt Morgans, too.

    =SUMIF($A$1:$A$350,"*Capt*",$B$1:$B$350)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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