How do I sum columns separating out the dates

  • Thread starter Thread starter soconfused
  • Start date Start date
S

soconfused

I have a spreadsheet that looks something like this:

1 01/02/08
..2 01/06/08
..8 12/15/08
15 02/07/09
1.7 02/22/09
10 05/06/09

I want to add the first column (which is hours), but I need them added by
mm/yy.

Thanks,
 
Try the below to retrive the sum for Jan 08 (01/08)

=SUMPRODUCT(--(TEXT(B1:B10,"mm/yy")="01/08"),A1:A10)

If this post helps click Yes
 
That helped a bit, but my spread sheet is a couple hundred lines long with
dates ranging from 07 through the present and I'd like to make a grid and add
them all together.
 
Create a list of dates and custom format it to display mmm-yyyy to look like
below...
Then use the below formula in H1 and copy down as required

=SUMPRODUCT(--(TEXT($B$1:$B$100,"mmyy")=TEXT(G1,"mmyy")),$A$1:$A$100)


Col G Col H
Jan-2007 =
Feb-2007 =
Mar-2007
Apr-2007
May-2007
Jun-2007
Jul-2007
Aug-2007
Sep-2007
Oct-2007

If this post helps click Yes
 
Hi,

you could use a pivot table here. Drag dates to the row area and hours to
the data area. Now go to any cell in the date columns (of the pivot table)
and on the pivot table, go to Pivot table > Group and Outline > Group. The
group box will come up and here you can select months and years.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top