Excel Formulas/VB: Show total based on date entry

Question

Hi, all.

I'm trying to accomplish something in Excel that I'm pretty sure requires some VB work, which I've never actually done, before.

I've got a spreadsheet with two columns that represent the number of processed records, and the date the records were processed. In some cases, the records can be processed in multiple batches, so the document looks something like this:

33 4/1/2009
22 4/1/2009
12 4/2/2009
13 4/4/2009
36 4/4/2009

I'm trying to add a new set of columns that contain a date, and shows the total number of records for that date, automagically:

4/1/2009 55
4/2/2009 12
4/3/2009 0
4/4/2009 49

I know how to do this algorithmically, and I could probably manipulate the spreadsheet outside of Excel, but I'm trying to do this in the live spreadsheet, and am a bit bewildered as to how to pull it off.

Any ideas?

Thanks! IVR Avenger

Answer 1

Will the SUMIF function work for you? SUMIF([range],[criteria],[sum_range]) I think you could set range = the set of cells containing dates in your first listing, criteria would be the cell containing the date in the second listing, and sum_range would be the counts in the first column of your first listing.

Answer 2

Assuming your dates are in column B and the numbers to be accumulated are in A, you could try something like this:

  | A  |   B      |    C                D
1 | 33 | 4/1/2009 | =MIN(B:B) | {=SUM(IF(B1:B5=C1,A1:A5,0))} |
2 | 22 | 4/1/2009 | =C1+1     | {=SUM(IF(B1:B5=C2,A1:A5,0))} |
3 | 12 | 4/2/2009 | =C2+1     | {=SUM(IF(B1:B5=C3,A1:A5,0))} |
4 | 13 | 4/4/2009 | =C3+1     | {=SUM(IF(B1:B5=C4,A1:A5,0))} |
5 | 36 | 4/4/2009 | =C4+1     | {=SUM(IF(B1:B5=C5,A1:A5,0))} |

Note the {} which signifies an array formula (input using Control-Shift-Enter) for any non-trivial amount of data it's heaps faster than SUMIF().

I'd be inclined to define dynamic names for the A1:A5 and B1:B5 parts, something like

=OFFSET(A1,0,0,COUNT(A:A),1)

so that I didn't have to keep fixing up my formulae.

There's still a manual element: adding new rows for extra dates, for example - that might be a good place for a little VBA. Alternatively, if you can get away with showing, for example, the last 90 days' totals, then you could fix the number of rows used.

Answer 3

I would suggest using a Pivot Table. Put the dates into the row area and 'sum of' records in the data area. Nothing in the columns area.

A pivot table will be more dynamic than a formula solution because it will only show you dates that exist.