Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-conditional expense tracking problem
In 1 sheet I have an account register as it appears in a checkbook:
Date, Payee, Classification, Debit, Credit, Balance In sheet 2 I have columns designated as pay days which is every 2 weeks and rows designated Classification. The classifications available in sheet 1 are derived from the row titles in sheet 2 so they are identical. I want the sum of each transaction for each Classification falling within the applicable 2 week period in sheet 2 from sheet 1. I can summarize the Classifications on a monthly basis using SUMIF but I haven't figured out how to gather data from within a range of dates. I haven't figured out how to use SUMPRODUCT either, if that is in fact the correct function to use. Please help. Thanks, Alex |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-conditional expense tracking problem
Assuming your data withe headers is in Sheet1:A1:F999
Summary in sheet2 has date Column heders and Classification Row headers Sheet2 has dummy column header in col A =SUMPRODUCT((Sheet1!$A$2:$A$999<=B$1)*(Sheet1!$A$2 :$A$999A$1)*(Sheet1!$C$2: $C$999=$A2)*(Sheet1!$E$2:$E$999-Sheet1!$D$2:$D$999)) See example: http://cjoint.com/?ejkq1bSSPG HTH -- AP "Alex" a écrit dans le message de ... In 1 sheet I have an account register as it appears in a checkbook: Date, Payee, Classification, Debit, Credit, Balance In sheet 2 I have columns designated as pay days which is every 2 weeks and rows designated Classification. The classifications available in sheet 1 are derived from the row titles in sheet 2 so they are identical. I want the sum of each transaction for each Classification falling within the applicable 2 week period in sheet 2 from sheet 1. I can summarize the Classifications on a monthly basis using SUMIF but I haven't figured out how to gather data from within a range of dates. I haven't figured out how to use SUMPRODUCT either, if that is in fact the correct function to use. Please help. Thanks, Alex |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multi-conditional expense tracking problem
Thank you. I don't understand it exactly but it works and that's good enough
for me. "Ardus Petus" wrote: Assuming your data withe headers is in Sheet1:A1:F999 Summary in sheet2 has date Column heders and Classification Row headers Sheet2 has dummy column header in col A =SUMPRODUCT((Sheet1!$A$2:$A$999<=B$1)*(Sheet1!$A$2 :$A$999A$1)*(Sheet1!$C$2: $C$999=$A2)*(Sheet1!$E$2:$E$999-Sheet1!$D$2:$D$999)) See example: http://cjoint.com/?ejkq1bSSPG HTH -- AP "Alex" a écrit dans le message de ... In 1 sheet I have an account register as it appears in a checkbook: Date, Payee, Classification, Debit, Credit, Balance In sheet 2 I have columns designated as pay days which is every 2 weeks and rows designated Classification. The classifications available in sheet 1 are derived from the row titles in sheet 2 so they are identical. I want the sum of each transaction for each Classification falling within the applicable 2 week period in sheet 2 from sheet 1. I can summarize the Classifications on a monthly basis using SUMIF but I haven't figured out how to gather data from within a range of dates. I haven't figured out how to use SUMPRODUCT either, if that is in fact the correct function to use. Please help. Thanks, Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Conditional Formatting problem | Excel Discussion (Misc queries) | |||
Count and Sum with Conditional Formatting Problem | Excel Worksheet Functions | |||
Problem with Conditional format deletion | Excel Discussion (Misc queries) | |||
help please with conditional format problem | Excel Worksheet Functions |