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

[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),SUMIF(E:E,"Furnature",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),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),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!

 

[T. Valko]

Try this:

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

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.

 

[mvollmers]

Excellent! That was exactly what I needed. Guess it's a lot easier when you
have the right function. Thanks for the help!

 

[T. Valko]

You're welcome. Thanks for the feedback!