problem with excel logic, too complicated of a function set???

  • Thread starter Thread starter mvollmers
  • Start date Start date
M

mvollmers

Hi, all. I'm not real robust on complicated excel logic, and so I'm not sure
if I can do what I want or not. here's the scoop. Excel 07, windows XP Pro
machine. I'm making my own budget sheet, and I have a table setup with date,
cost, & category. I have a list defined with all these categories, and then
I sweep the table and sum the ones for my totals. for example. home
category consists of two items, so the total is
=SUM(SUMIF(E:E,"home Improvement",D:D),SUMIF(E:E,"Furnature",D:D))
some categories have one item, some have more.

Now, I am trying to break this down by weekly spending, so I tried to filter
by date first.
=IF(DAY(A:A)<=7,SUMIF(E:E,"Salary",D:D),0)
this just returns the entire month's salary and puts it in the week column
that I am in. What I am looking for is to have my total columns off to the
side, and I want it to first filter by day and then sum. I tried
=SUMIF(IF(DAY(A:A)<=7,(E:E,"Salary",D:D),0))
trying to drive the if to push the sum, but it doesn't like it. I can think
it out with code, but I always have a hard time translating that to excel
formulas. It would go like:
For (all items in stack)
If Day <= 7 and Category = Salary
wk1total = wk1total + salary
If (Day > 7 and Day <= 14) and Category = Salary
wk2total = wk2total + salary
etc

Hope this is clear enough. Thanks!
Suggestions? Thanks!
 
Try this:

=SUMPRODUCT(--(DAY(A1:A100)<=7),--(E1:E100="salary"),D1:D100)

If you're using Excel 2007 you can use entire columns as range references
*but* unless you're actually filling those entire ranges I would use the
smallest specific range as needed. Every cell referenced is evaluated. If
you're only using 500 rows then you're wasting resources by evaluating all
those empty rows.
 
Excellent! That was exactly what I needed. Guess it's a lot easier when you
have the right function. Thanks for the help!
 
Back
Top