VGMaps
November 17, 2017, 04:05:14 PM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 
   Home   Help Search Login Register  
Pages: [1]   Go Down
  Print  
Author Topic: Microsoft Excel question - need help with totalling different items!  (Read 2892 times)
0 Members and 1 Guest are viewing this topic.
JonLeung
Administrator
*****
Offline Offline

Posts: 3129


« on: April 15, 2011, 07:33:16 AM »

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.
« Last Edit: April 15, 2011, 07:44:37 AM by JonLeung » Logged
JonLeung
Administrator
*****
Offline Offline

Posts: 3129


« Reply #1 on: April 15, 2011, 08:37:21 AM »

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

I'll keep this up in case someone else asks...  Tongue
Logged
DarkWolf
Hero Member
*****
Offline Offline

Posts: 621



« Reply #2 on: April 18, 2011, 07:46:50 AM »

It always amazes me the things people can make Excel do.  Personally, I prefer my relational databases and SQL.   Tongue
Logged
Maxim
Hero Member
*****
Offline Offline

Posts: 972



« Reply #3 on: April 18, 2011, 10:00:57 AM »

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

Posts: 330



« Reply #4 on: April 18, 2011, 09:18:34 PM »

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.
Logged
Pages: [1]   Go Up
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.20 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!