VGMaps
April 21, 2018, 10:29:13 AM *
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 3064 times)
0 Members and 1 Guest are viewing this topic.
JonLeung
Administrator
*****
Offline Offline

Posts: 3151


« 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: 3151


« 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!
VGMaps.com: The Video Game Atlas

 

Pixel WarehouseVGMuseum.com

 

VGMaps.com is registered with and hosted by

Go Daddy.

Supported by you.

Some advertising of and on VGMaps.com is handled by

Project Wonderful.

2002-2018 Jonathan Leung.

VGMaps.com is an archive of video game maps up since May 6, 2002. Optimized for at least 800 x 600 resolution.

This site does not contain commercial ROMs or any other illegal materials. All directly "ripped" game images are the property of their respective copyright holders. This web site and compass logo are copyrighted by Jonathan Leung 2002-2018.