New Contributor

# Excel - Newbie - "If" Help

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.

2 Replies

# Re: Excel - Newbie - "If" Help

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.

# Re: Excel - Newbie - "If" Help

@Hans Vogelaar

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