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.
