Excel - Newbie - "If" Help

New Contributor

I have been requested to create an inventory Excel Sheet.  I need to track Monitors, Mouse, Keyboard, Cable. Each will begin with a fix number (example 10, 20, 35, 15). I created a pulldown for each type of device (list name DEVICE) and another pulldown with 3 choices (Add, Borrow, Returned – list name STATUS).

If a device (DEVICE pulldown) is borrowed (STATUS pulldown), depending on the cell value (example 4 in E5), I want that cell number value to be subtracted from that device total.

If a specific device is Add/Returned, depending on the cell value, I want the cell number value to be increased to that device total.

Example: We begin with 10 monitors. 4 people borrow monitors, so the total is now 6. A few days later 1 person RETURNs a monitor, the total should now be 7. A week later we purchase (ADD) 2 more monitors, so the new total would be 9. A month later the remaining 3 monitors are RETURNed – so the new grand total should be 12.

So I am guessing I need a formula to reflect the device chosen compared to status and the number in a specified cell to reflect the total number.

I hope this makes sense.

I know some basic Excel formulas (Sum=B1-B2), but since this has many variables I am unsure what type of statement/formula to use. Since this will be a living document, I need it to be consistent over multiple lines. 

Any help would be greatly appreciated.

I thank you in advance. 

2 Replies

@Tom Casola 

In F3:

 

=F2-SUMIFS($E$6:$E$10000, $C$6:$C$10000, F1, $D$6:$D$10000, "BORROW")+SUMIFS($E$6:$E$10000, $C$6:$C$10000, F1, $D$6:$D$10000, "ADD")+SUMIFS($E$6:$E$10000, $C$6:$C$10000, F1, $D$6:$D$10000, "RETURNED")

 

Fill to the right to I3.

@Hans Vogelaar 

I just saw your reply. I will give it a try. Thanks for responding!