software

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 lt;B2gt; lt;C2gt;
Category 2 lt;B3gt; lt;C3gt;

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]
www.andrewconnell.com
www.andrewconnell.com/mvp
Enter the following formula array (cntrl-shift enter) into each of the four
cells in your matrix

=SUM(IF($A2amp;MONTH(B$1)amp;YEAR(B$1)=Sheet1!A1:A3amp;MONT H(Sheet1!$B$1:$B$3)amp;YEAR(Sheet1!$B$1:$B$3),Sheet1! $C$1:$C$3,quot;quot;))

The data on Sheet1 begins in cell a2 as I put heading columns above your data.
--
HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758quot;AC [MVP MCMS]quot; wrote:

gt; I have a table with three columns: category, date, amount. On another summary
gt; sheet, I'm trying to sum up all the amounts based on the category for each
gt; month. So, for example my source data is the following:
gt;
gt; Category 1 2/23/2005 1.23
gt; Category 2 2/27/2005 5.21
gt; Category 1 2/28/2005 3.67
gt;
gt; And my summary sheet has the following
gt;
gt; FEB-2005 FEB-2006
gt; Category 1 lt;B2gt; lt;C2gt;
gt; Category 2 lt;B3gt; lt;C3gt;
gt;
gt; Cell B2 would contain the sum of (1.23 2.67) and so on. I've been fighting
gt; with SUMIF and SUMPRODUCT, but I can't get anything to come out. Not sure
gt; if I needed it, but I created a few hidden rows that contain the first and
gt; last date of each month range, but it's not helping. Ideas?
gt;
gt; --
gt; -AC [MVP MCMS]
gt; www.andrewconnell.com
gt; www.andrewconnell.com/mvp
gt;
gt;
gt;

Hi!

Assume this table is on Sheet1:

gt; Category 1 2/23/2005 1.23
gt; Category 2 2/27/2005 5.21
gt; 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,quot;mmm-yyyyquot;)=B$1),Sheet1!$C$1:$C$3)

Biff

quot;AC [MVP MCMS]quot; gt; wrote in message .com...
gt;I have a table with three columns: category, date, amount. On another
gt;summary sheet, I'm trying to sum up all the amounts based on the category
gt;for each month. So, for example my source data is the following:
gt;
gt; Category 1 2/23/2005 1.23
gt; Category 2 2/27/2005 5.21
gt; Category 1 2/28/2005 3.67
gt;
gt; And my summary sheet has the following
gt;
gt; FEB-2005 FEB-2006
gt; Category 1 lt;B2gt; lt;C2gt;
gt; Category 2 lt;B3gt; lt;C3gt;
gt;
gt; Cell B2 would contain the sum of (1.23 2.67) and so on. I've been fighting
gt; with SUMIF and SUMPRODUCT, but I can't get anything to come out. Not sure
gt; if I needed it, but I created a few hidden rows that contain the first and
gt; last date of each month range, but it's not helping. Ideas?
gt;
gt; --
gt; -AC [MVP MCMS]
gt; www.andrewconnell.com
gt; www.andrewconnell.com/mvp
gt;
gt;