Forum Discussion
Need help with a formula
Hello.
I need some help with a formula for counting the total quantity of an item present on a sheet. I have an equipment sheet with similar information to what is shown below that states the number of devices in each room within a large office building.
SHEET 1
| A | B | C |
| Room | Item | Quantity |
| 1 | Computer | 5 |
| Phone | 3 | |
| 2 | Computer | 2 |
| 3 | Computer | 7 |
| Phone | 4 |
I want to add a different sheet that shows the total quantity of each item overall in the entire building. Something like this:
SHEET 2
| Item | Quantity |
| Computer | 14 |
| Phone | 7 |
I am looking for a formula that searches sheet 1 column B for everything that says "Computer" and then counts the quantity that is in column C adjacent. Is this a possible function?
Thank you for your help.
7 Replies
The easiest way to do that is to select the range on Sheet 1, then create a pivot table (it's the first button on the Insert tab of the ribbon).
Add Item to the Rows area and Quantity to the Values area.
If you prefer a formula:
=SUMIF('Sheet 1'!$B$2:$B$6, A2, 'Sheet 1'!$C$2:$C$6)
Change Sheet 1 to the real name of the first sheet, adjust the ranges if necessary, then fill down.
Edited to add missing ! in 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?
