Need help with a formula

Copper Contributor



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.






I want to add a different sheet that shows the total quantity of each item overall in the entire building. Something like this:






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

@Hans Vogelaar 


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:


ItemQuantityIn StockNeed to Order


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.
Yes, this is working perfectly now. Thank you very much for your help in creating this.


Sorry about the missing !

I wrote the formula directly in the post instead of testing it :(