Thread: Sum Data by Date

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

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.

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

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.

=SUMIFS($A$1:$A$1000,">="&E1,$A$1:$A$1000,"<"&E1+1,$B$1:$B$1000)

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

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

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:

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