Need help with a formula

[Steven560]

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.

[#2]

@Steven560 

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

[#3]

@HansVogelaar 

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

[#4]

@Steven560 

Did you try the formula that I suggested?

[#5]

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.

[#6]

I played with it more. Your formula was missing a second ! for the sum range. This looks to be working correctly. now.

[#7]

Yes, this is working perfectly now. Thank you very much for your help in creating this.

[#8]

@Steven560 

Sorry about the missing !

I wrote the formula directly in the post instead of testing it 😞