Forum Discussion

Copper Contributor

Sep 03, 2020

Formula Help To calculate inventory

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 i...

Sample Inventory Sheet.xlsx84 KB

excel

Formulas and Functions

Bennadeau

Iron Contributor

Sep 03, 2020

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!

Sample Inventory Sheet.xlsx93 KB

DZarc9820
Copper Contributor
Sep 03, 2020
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!
- SergeiBaklan
  Diamond Contributor
  Sep 03, 2020
  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.
  
  Sample Inventory Sheet.xlsx88 KB
- Bennadeau
  Iron Contributor
  Sep 03, 2020
  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