How do I sum columns separating out the dates

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,
 
J

Jacob Skaria

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
 
S

soconfused

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.
 
J

Jacob Skaria

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
 
A

Ashish Mathur

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top