View Full Version : Need Excel Help
10-28-2008, 07:34 PM
Creating a spending log...column B has the amount, column F has the category from a list of 9 categories.
What I want to do is get a total based on the categories. So if any row in the F column is labeled "GAS" then I want it to sum all the corresponding amounts in the same B column.
I hope I explained that right....maybe this will help
so BEER = 9 (B3+B5) and GAS = 10 (B1+B4) and FOOD = 4 (B2)
Thanks for the help peeps..
10-28-2008, 08:02 PM
There are a few ways to do this. The easy way is listed below. The sexier way requires a bit more Excel mojo so if you want to go that route we may need to discuss "off-line" so as not to bore everyone.
The easy way-
Make new columns (say H, I, J for your example) that will use an "IF" statement to create easy to sum columns of your data. The "IF" statements would be something like this:
All you need to do is use the "SUM" function to add all the numbers in your columns H, I, and J and you are done. The best part about it is that the "SUM" function can be placed anywhere you need it and not necessarily in the H, I, and J columns.
You can use as many columns as you need to accommodate your spreadsheet. When I have trouble-shot my spreadsheet and made sure it calculates properly I usually hide these types of columns so that they are not visible.
Good luck. By the way. . . you are not buying enough beer!:wink:
10-28-2008, 08:24 PM
=sumif(range, criteria, values)
so for beer you would write
=sumif(f2:f99; "beer"; b2:b99)
let me know if that doent work (or you need more help)
edit:zonian's solution works too, but as he says - requires extra columns
10-29-2008, 02:30 PM
You also need a means of making said speadsheet invisible from SWMBO :biggrin:
What the missus doesn't know can't hurt her
10-29-2008, 03:06 PM
mdunn - thanks for that. That's what I had tried but only as IF, not SUMIF...duh
Zonian - thanks for the suggestion.
PaulH - it's called "HIDE"...in my version it's under View>Hide. In the previous (XP) version it's View>Sheet>Hide. Not that I've needed to do that :tongue_sm ...besides, isn't it easier to ask forgiveness than permission? :001_rolle
Thanks again for the help guys....I really appreciate it.
Powered by vBulletin® Version 4.2.0 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.