Solved

Excel Simple Inventory Tracking

Posted on 2006-07-07
4
4,643 Views
Last Modified: 2007-12-19
I have a spreedsheet my employees fill out to tell me what supplies they have taken
A               B                C               D
DATE  |  EMPLOYEE  |  ITEM  |  AMOUNT TAKEN

I did not limit them to drop downs but will just have to have them type EXACLTY as inventory list has the item listed

I have a nother spreedsheet that shows me the totlas of each item (for reporting)

I am trying to write an IF statement  for the amount taken column that says .
IF Any cell in column C = MUGS  then sum all  "amounts taken" in column D that are for mugs only
and the same this for each item

Could someone help me with this IF SUM statement or suggest another way - this is such an each inventory - I can't seem to figure out why it is giving me such problems

I want my results to be:
ITEM            BEGINNING BALANCE     AMOUNT TAKEN             AMOUNT AVAILABLE
                  
MUGS                     114                           4                               110
BEACH BALLS      26             1                              25
KEY CHAINS      47             1                              46
NITE LITES      17             2                              15
LETTER OPENER      52             3                              49
SAFETY KITS      53              4                             49
STRESS BALLS      119              3                             116
CLIPS                      1580              2                             1587
YELLOW ICE SCRAPER 231                                           231
BLUE ICE SCRAPER      146            146

0
Comment
Question by:swicked77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Accepted Solution

by:
KathrynGZ earned 25 total points
ID: 17063304
Try using DSUM. Search in Excel help for DSUM, then scroll down to see the sample spreadsheet and sample formulas. One of the DSUM sample formulas looks similar in principle to what you're trying to do.

HTH,

Kathryn
0
 

Assisted Solution

by:cnxmax
cnxmax earned 25 total points
ID: 17084304
In the section of your spreadsheet that you labeled "I want my results to be:" put the following formula in the "Amount Taken" column (which I am assuming is the "C" column):    =SUMIF(C3:D15,A18,D3:D15).

Change "C3:D15" to the cell range your employees use to record the item they took (column C in your example) AND the amount they took (column D in our example)...(also change the rows in the range from 3-15 to whatever rows your employees use to record their takeage).

Change "A18" to the whatever column your items are listed  in the section you labeled "I want my results to be:"...you labeled this column "ITEM"  (Also change the row from 18 to whatever row you put the formula in).

Change "D3:D15" to the cell range your employees use to record the amount they took.  (also change the rows in the range from 3-15 to whatever rows your employees use to record their takeage).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question