Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Summing...
I have a table with three columns: category, date, amount. On another summary
sheet, I'm trying to sum up all the amounts based on the category for each month. So, for example my source data is the following: Category 1 2/23/2005 1.23 Category 2 2/27/2005 5.21 Category 1 2/28/2005 3.67 And my summary sheet has the following FEB-2005 FEB-2006 Category 1 <B2 <C2 Category 2 <B3 <C3 Cell B2 would contain the sum of (1.23+2.67) and so on. I've been fighting with SUMIF and SUMPRODUCT, but I can't get anything to come out. Not sure if I needed it, but I created a few hidden rows that contain the first and last date of each month range, but it's not helping. Ideas? -- -AC [MVP MCMS] http://www.andrewconnell.com http://www.andrewconnell.com/mvp |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Summing...
Enter the following formula array (cntrl-shift enter) into each of the four
cells in your matrix =SUM(IF($A2&MONTH(B$1)&YEAR(B$1)=Sheet1!A1:A3&MONT H(Sheet1!$B$1:$B$3)&YEAR(Sheet1!$B$1:$B$3),Sheet1! $C$1:$C$3,"")) The data on Sheet1 begins in cell a2 as I put heading columns above your data. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "AC [MVP MCMS]" wrote: I have a table with three columns: category, date, amount. On another summary sheet, I'm trying to sum up all the amounts based on the category for each month. So, for example my source data is the following: Category 1 2/23/2005 1.23 Category 2 2/27/2005 5.21 Category 1 2/28/2005 3.67 And my summary sheet has the following FEB-2005 FEB-2006 Category 1 <B2 <C2 Category 2 <B3 <C3 Cell B2 would contain the sum of (1.23+2.67) and so on. I've been fighting with SUMIF and SUMPRODUCT, but I can't get anything to come out. Not sure if I needed it, but I created a few hidden rows that contain the first and last date of each month range, but it's not helping. Ideas? -- -AC [MVP MCMS] http://www.andrewconnell.com http://www.andrewconnell.com/mvp |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Summing...
Hi!
Assume this table is on Sheet1: Category 1 2/23/2005 1.23 Category 2 2/27/2005 5.21 Category 1 2/28/2005 3.67 Enter the coulmn headers: FEB-2005, FEB-2006 as TEXT entries, not FORMATTED DATES Enter this formula in B2 then copy across then down: =SUMPRODUCT(--(Sheet1!$A$1:$A$3=$A2),--(TEXT(Sheet1!$B$1:$B$3,"mmm-yyyy")=B$1),Sheet1!$C$1:$C$3) Biff "AC [MVP MCMS]" wrote in message .com... I have a table with three columns: category, date, amount. On another summary sheet, I'm trying to sum up all the amounts based on the category for each month. So, for example my source data is the following: Category 1 2/23/2005 1.23 Category 2 2/27/2005 5.21 Category 1 2/28/2005 3.67 And my summary sheet has the following FEB-2005 FEB-2006 Category 1 <B2 <C2 Category 2 <B3 <C3 Cell B2 would contain the sum of (1.23+2.67) and so on. I've been fighting with SUMIF and SUMPRODUCT, but I can't get anything to come out. Not sure if I needed it, but I created a few hidden rows that contain the first and last date of each month range, but it's not helping. Ideas? -- -AC [MVP MCMS] http://www.andrewconnell.com http://www.andrewconnell.com/mvp |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional summing | Excel Discussion (Misc queries) | |||
Conditional Summing (Sumif? Countif?) | Excel Worksheet Functions | |||
Conditional Summing | Excel Discussion (Misc queries) | |||
Conditional summing with large amounts of data | Excel Worksheet Functions | |||
conditional Summing | Excel Worksheet Functions |