Microsoft Excel question - need help with totalling different items!

[JonLeung]

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.

[JonLeung]

Never mind.  My friend Michelle pointed out to me the "SUMIF" function.  Thanks, Michelle!

I'll keep this up in case someone else asks... 

[DarkWolf]

It always amazes me the things people can make Excel do.  Personally, I prefer my relational databases and SQL.   

[Maxim]

Sadly the original site is gone and the wayback machine didn't get it all, but here's what we're really thinking.

[bustin98]

Sadly the original site is gone and the wayback machine didn't get it all, but here's what we're really thinking.

I had someone give me an excel file thinking they were all smart or something when I had to convert it into a database. It's still a nightmare for me.