Forum Discussion
Need help with a formula
I should have clarified why I want a formula over a pivot table.
I want to add additional columns that will allow me to subtract from the totals. For example, this:
| A | B | C | D |
| Item | Quantity | In Stock | Need to Order |
| Computer | 14 | 10 | 4 |
| Phone | 7 | 0 | 7 |
The only column that I would input information in is column C. This will allow for better management of equipment inventory. A pivot table will work for basic counts, but any information in C and D will not correlate (or the formulas will not work) if a user changes the view within the pivot table to show only 1 item. It is for this reason that I thought a formula would be better.
As I type this though, I assume I can simply lock the pivot table to only always show all items and not allow a user to hide any. I will still post this if anyone has other suggestions on how best to accomplish my goal here.
Thank you
Did you try the formula that I suggested?
- I did. I input the correct sheet name and I changed the range from 6 to 300 solely to allow for any additions to be added. The formula returns an error.
- I played with it more. Your formula was missing a second ! for the sum range. This looks to be working correctly. now.
Sorry about the missing !
I wrote the formula directly in the post instead of testing it 😞