Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

Excel Simple Inventory Tracking

Posted on 2006-07-07
Medium Priority
4,652 Views
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
Question by:swicked77

Accepted Solution

KathrynGZ earned 100 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

cnxmax earned 100 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

Question has a verified solution.

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

When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this tutorial, we’re going to learn how to convert Youtube to mp3 for Free. We'll show you how easy it is to make an mp3 from your video clips so that you can enjoy them offline.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses
Course of the Month15 days, 13 hours left to enroll