Excel 2007 : Sum Data by Date

[brian1968]

I have a simple 2 column sheet that I want to total by date. The difficulty that I have it that the date includes time.

Date/Time | Count
8/19/2010 06:22 | 3
8/19/2010 07:33 | 8
8/19/2010 08:44 | 4
8/20/2010 06:22 | 7
8/20/2010 07:33 | 9
8/20/2010 08:44 | 3

My sheet has over 3000 records and do not want to manual calculate as I know there is an easier way, but cannot figure it out. What I need to do is to total up the count for each day like...

8/19/2010 | 15
8/20/2010 | 19

I'm sure this is simple to someone.

Brian

ADD:
After submitting my question, I thought that eventually I would want to also sum total by month so I might as well ask now.

Example:

January | 89
February | 112
March | 72
etc...

[JBeaucaire]

If the date were listed in E1, then this formula in F1:

=SUMPRODUCT((INT($A$1:$A$100)=E1)*($B$1:$B$100))

Expand the matching ranges as needed, do not try and use a whole column, just adjust the range to what is needed.

[brian1968]

Thank you so much Jerry. The formula worked like a charm. You are awesome!!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[DonkeyOte]

Brian, you should really investigate the use of Pivot Tables.

Attached is an example based on your sample data.

If you opt against Pivot Tables (most efficient option) then you should - given your use of XL2007 - use SUMIFS rather than SUMPRODUCT esp. if you have lots of unique dates to return data for - the latter is not efficient used en masse or with big data sets.

Please Login or Register  to view this content.

If backwards compatibility is an issue then 2 SUMIF functions would be equally viable (or a new field at source which normalises the dates to 1st of month thereby allowing for basic SUMIF)

FWIW - re: SUMPRODUCT

Please Login or Register  to view this content.

given the explicit coercion taking place in the above (*) it's imperative that the sum_range (B1:B100) contain no non-numeric values (eg text strings like header values)

The alternative / preferred option would be to avoid the explicit coercion of sum_range altogether unless explicitly required, ie:

Please Login or Register  to view this content.

[brian1968]

This is totally awesome. I've never really investigated Pivot Tables before. The attachment was so helpful to show me the possibilities that I will have to learn this feature of Excel.Thank you so very much.

Brian