Hey guys, question about Microsoft Excel here. I could really use an answer...

I've solved a lot of tricky stuff in Excel before, but I'm baffled that I can't find a function for simply adding quantities of different items.

For example:

**Fruit - Quantity**

Apples - 2

Bananas - 3

Coconuts - 8

Bananas - 1

Coconuts - 7

Apples - 4

Apples - 6

Coconuts - 5

Bananas - 9

Let's say I have the above, with the fruit names in one column and the quantity of them in the adjacent column. I want to be able to total each fruit type.

Now here's the catch: I can't rearrange the rows by sorting, and I don't want to have to do any manual work after figuring this out the first time, at least no more than just inserting a column or another worksheet every time I get the data dump of columns similar to the above every month or whatever, and have all the totals I need. I know how to use macros, but I'm not sure that they can work with Auto-filter or PivotTables without still some manual involvement.

I can think of two ways, but I'm hoping someone can come up with a better suggestion...

**1.** One way involves making separate columns with headers for each of the fruits. Using a simple IF/THEN function comparing the fruit name column with each header, I can say that if they equal, to transpose the quantity into that column. Then it's a simple sum of each column for each fruit. However, this requires a column for each fruit, and in my real scenario, I have dozens, if not hundreds of different items to tally, so this is not ideal.

**2.** The other way involves the DSUM function. But the weird thing is that I have to have a separate column reiterating what I want to total, so in addition to the example table above, let's say I have this column somewhere on the spreadsheet:

**Fruit**

Apples

Bananas

The DSUM function requires a range (not a simple string of text or a single cell) for its criteria for some stupid reason, so I can select "Fruit" down to "Apples" to total just the Apples. I can also select "Fruit" down to "Bananas" to total the Apples and the Bananas together. But if I want to total just the Bananas? Can't. Not unless I have another column. So then I'd have to do something that looks like this:

**Fruit - Fruit**

Apples - Bananas

So then I end up with a separate column for each fruit again (it can't be a row for each, for some stupid reason), and then it's just like in my first scenario. Actually, it's worse because now I have to waste the whole top row repeating "Fruit".

I feel like I'm overlooking a function that's incredibly easy, because I don't see why something like Excel wouldn't have something as basic as adding certain numbers based on the text in an adjacent column.

Any help would be appreciated.