Formula Help To calculate inventory

%3CLINGO-SUB%20id%3D%22lingo-sub-1632605%22%20slang%3D%22en-US%22%3EFormula%20Help%20To%20calculate%20inventory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632605%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%20I%20have%20an%20inventory%20sheet%20(sample%20attached)%20where%20I%20need%20to%20calculate%20the%20total%20cost%20of%20each%20category%20(ie.%20beer%2C%20wine%2C%20liquor).%26nbsp%3B%20The%20challenge%20is%20that%20for%20ease%20of%20taking%20inventory%20the%20sheet%20isn't%20set%20up%20where%20all%20beer%20is%20together%2C%20all%20wine%20is%20together%2C%20etc.%26nbsp%3B%20Instead%2C%20its%20by%20supplier.%26nbsp%3B%20So%2C%20I%20have%20a%20column%20where%20it%20take%20total%20count%20X%20price%20and%20gives%20me%20my%20inventory%20cost%20for%20each%20line%20item%20(ie.%20White%20Claw%20Berry%2C%20White%20Claw%20Mango%2C%20etc.)%26nbsp%3B%20Then%20I%20have%20a%20column%20that%20identifies%20the%20line%20item%20as%20beer%2C%20wine%20or%20liquor.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20get%20my%20total%20costs%20by%20category%20(beer%2C%20wine%20and%20liquor)%20I%20need%20to%20add%20up%20all%20beer%20line%20items%2C%20all%20wine%20line%20items%20etc.%26nbsp%3B%20I%20am%20doing%20this%20manually%20via%20inputting%20a%20formula%20each%20week...%20basically%20%3Dsum(_)%2Bsum()%2BSum()...%20its%20very%20tedious.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20assume%20there%20is%20a%20way%20to%20put%20a%20formula%20in%20a%20cell%20and%20say%20add%20up%20all%20the%20wine%2C%20add%20up%20all%20the%20beer%2C%20etc.%20%3F%3F%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20on%20how%20I%20can%20do%20this%20would%20be%20greatly%20appreciated.%20It%20will%20make%20the%20process%20so%20much%20easier%20for%20me.%26nbsp%3B%20When%20you%20look%20at%20the%20file%20you'll%20see%20what%20I'm%20wanting%20to%20do%20more%20easily.%26nbsp%3B%20Thanks!!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1632605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1632687%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20To%20calculate%20inventory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632687%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780362%22%20target%3D%22_blank%22%3E%40DZarc9820%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ETry%20this%20...%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20problem%20is%20that%20in%20your%20description%20column%20(column%20I)%2C%20things%20are%20not%20all%20spelled%20the%20same.%20For%20instance%2C%20%22mix%22%20and%20%22mixes%22%20or%20sometimes%20you%20have%20some%20trailing%20spaces%20after%20the%20word.%20Once%20you%20fix%20them%20all%20in%20accordance%20to%20what%20you%20see%20in%20cell%20J4%20to%20N4%20your%20math%20will%20work%20fine.%20(it%20has%20to%20be%20an%20exact%20match).%20I%20fixed%20a%20few%20but%20some%20are%20missing.%20Have%20fun!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!  I have an inventory sheet (sample attached) where I need to calculate the total cost of each category (ie. beer, wine, liquor).  The challenge is that for ease of taking inventory the sheet isn't set up where all beer is together, all wine is together, etc.  Instead, its by supplier.  So, I have a column where it take total count X price and gives me my inventory cost for each line item (ie. White Claw Berry, White Claw Mango, etc.)  Then I have a column that identifies the line item as beer, wine or liquor.  

To get my total costs by category (beer, wine and liquor) I need to add up all beer line items, all wine line items etc.  I am doing this manually via inputting a formula each week... basically =sum(_)+sum()+Sum()... its very tedious.

I assume there is a way to put a formula in a cell and say add up all the wine, add up all the beer, etc. ??   

Any help on how I can do this would be greatly appreciated. It will make the process so much easier for me.  When you look at the file you'll see what I'm wanting to do more easily.  Thanks!!! 

4 Replies

Hi @DZarc9820,

Try this ... 

One of the problem is that in your description column (column I), things are not all spelled the same. For instance, "mix" and "mixes" or sometimes you have some trailing spaces after the word. Once you fix them all in accordance to what you see in cell J4 to N4 your math will work fine. (it has to be an exact match). I fixed a few but some are missing. Have fun!

@Bennadeau  Thank you so much for your help!!! This will be so much easier than the way I have it currently set up!  You're awesome! 

You're welcome. And I found a spot where the word wine is not written, cell i251. That's why the sheet I sent doesn't balance by $12.

 @DZarc9820 

@DZarc9820 

As variant you may use PivotTable on last two columns filtering the blank on category. That's only to refresh when source data is changed.

image.png