Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the average using conditions in a logic function - problem | Excel Discussion (Misc queries) | |||
complicated problem I think Excel can handle | Excel Discussion (Misc queries) | |||
Adding in a piece of logic to my complicated formula! | Excel Worksheet Functions | |||
Can wildcards be used in the Logic Test of an Excel "IF" function? | Excel Worksheet Functions | |||
Excel Logic Function | Excel Discussion (Misc queries) |