Need Excel Help

[Truckman]

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

A...B...C...D...E...F
1...5.................GAS
2...4.................FOOD
3...2.................BEER
4...5.................GAS
5...7.................BEER

so BEER = 9 (B3+B5) and GAS = 10 (B1+B4) and FOOD = 4 (B2)


Thanks for the help peeps..

[Zonian]

Truckman,

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:

H1=IF($F1="BEER",$B1,0)
I1=IF($F1="FOOD",$B1,0)
J1=IF($F1="GAS",$B1,0)

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:

[mdunn]

=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

[PaulH]

You also need a means of making said speadsheet invisible from SWMBO :biggrin:
--
Paul

What the missus doesn't know can't hurt her

[Truckman]

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.