Thread: Summing Totals of Quantities

I am trying to create an excel spreadsheet that will sum up the number of selected items and then generate a total that takes the item, times the price, and gives me a total price.

For example, if I have a pricing table of:

Item Price
Item 1 1000
Item 2 2000

In my data list if have

User 1 Chose item 1
User 2 Chose Item 2
User 3 Chose Item 1

I want to display
Item 1 has 2 items
Item 2 has 2 items
Total cost is 4,000.

This can be hard-coded, but at this time I do not know what items or users will be selected. I have specified a general example, but I have attached a very specific spreadsheet. My problem is the hard coding in cell F37 and I want to make this a dynamic formula. I am using Excel 2007.

When I was originally working on this, i did not have the cost per item displayed in e27:e35. The pricing table is located on the other tab and originally i wanted the formula to pull the prices from looking into the pricing table.

Displaying the prices alongside the array made it a lot easier to calculate. But we may summerize the list and just show counts of sub categories and then the formula you provided would not work. (for example: New Laptops, New Desktops, Repurposed computers).