Excel sum by categories column

[marioletto]

hi all,
i'm strugling with someting that should be quite simple.

in my Excel sheet i have two colums:
expenses and categories. (categories are in a drop down and pulled from an other sheet)
i'm looking to find a way to calculate expenses by category possibly in a way that would update itself if i was to move one expense from one category to an other or rename a category.

please note that my excel knowledge is limited so the more detailed you solution, the more i will learn!

thanks in advance for your help,
regards
mario

 

[ETID]

Have you tried the pivot table tool?

 

[marioletto]

pivot table tool: i can't get it to work with only 2 variables.

if it was an sql query, it would be something like:
sum A1:A22 where B=transportation

thanks

 

[AnotherHiggins]

=>sum A1:A22 where B=transportation
Give this a shot:
[COLOR=blue white]=sumproduct(($B$1:$B$22="Transportation")*($A$1:$A$22))[/color]

Or you can replace "Transportation" with a referecnce to a cell that contains that value if you want.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SkipVought]

pivot table tool: i can't get it to work with only 2 variables."

???

Drag Category to ROW AREA

Drag Expenses to DATA AREA as Sum or Expenses.

???

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kpetursson]

have you tried the =sumif()
SUMIF(range to check for value,value to check,range you want the sum of)

a b c d
1 one 5 tee =sumif($a$1:$a$5,c1,$b$1:$b$5)
2 two 6 rat =sumif($a$1:$a$5,c2,$b$1:$b$5)
3 one 7 two =sumif($a$1:$a$5,c3,$b$1:$b$5)
4 tee 2 one =sumif($a$1:$a$5,c4,$b$1:$b$5)
5 rat 1 hat =sumif($a$1:$a$5,c5,$b$1:$b$5)
6
7 one =sumif(a1:a5,a7,b1:b5)

b7 will equal 12

d1 will = 2
d2 will = 1
d3 will = 6
d4 will = 12
d5 will = 0

hope this helps

Kevin Petursson

 

[marioletto]

=sumproduct(($B$1:$B$22="Transportation")*($A$1:$A$22))

Something like this would be nice!unfortunatlely this one gives me the #VALUE! error

 

[SkipVought]

What do you have in A1? Should NOT BE TEXT!

For that matter NO TEXT in A1:A22

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[marioletto]

pivot table tool: neat feature!
a little too complicated for my needs.

 

[SkipVought]

a little too complicated for my needs."

... indicates a lazy, not my way, user to me.

It would take me less than 10 seconds to construct a valid pivot table with your data!

You ought to be eager to assemble NEW tools in your toolbag.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[marioletto]

> What do you have in A1? Should NOT BE TEXT!
> For that matter NO TEXT in A1:A22

=sumproduct(($B$1:$B$22="Transportation")*($A$1:$A$22))
that's it, a little space was to problem!
now it works like a charm.
thanks.
m.

 

[marioletto]

Skip,
It's good to know that the pivot tables exhist, and for that i thank you. but i spend about one hour playing/learning pivot tables and they simply are not the tool for this task of mine. It's like you trying to convince me that taking a shower is a great way to to wash my hands. In addition SkipVought came up with the simple solution i was looking for in the first place.
sorry if i can't find any lazyness on my part.
thanks anyway for the help.


btw, for excel novices like me there is a nice pivot tables tutorial here:

http://www.microsoft.com/smallbusin...se_pivottables_excels_data_tool_du_jour.mspx)