Solved

Excel Simple Inventory Tracking

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

Accepted Solution

by:
KathrynGZ earned 25 total points
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This video shows how use content aware, what it’s used for, and when to use it over other tools.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now