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

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!

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

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.

--
Biff
Microsoft Excel MVP


"mvollmers" wrote in message
...
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!

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

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"mvollmers" wrote in message
...
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!

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mvollmers" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"mvollmers" wrote in message
...
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!