?
Solved

Excel Simple Inventory Tracking

Posted on 2006-07-07
4
Medium Priority
?
4,649 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 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

by:cnxmax
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

If your app took Google’s lash recently, here are the 5 most likely reasons.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

752 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