Solved

Excel Simple Inventory Tracking

Posted on 2006-07-07
4
4,646 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
This video shows how use content aware, what it’s used for, and when to use it over other tools.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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