aggregate multiple transactions with same date...

[Georgia Golfer]

Here I go again.....the sad thing is that I'm the best at excel around the office!!!!

I have a report that I can export to excel, tracking cash transactions. I want to aggregate on a daily basis without having to add sum after each day. I'll be looking at 3 months history, but shouldn't be an issue for this. I'll be cutting and pasting the data and will run the forumla on the same page. Basically, this is what I'm looking for:

COL A COL B
10/1/09 $100
10/1/09 $500
10/1/09 $100
10/2/09 $50
10/2/09 $50
10/3/09 $75
10/3/09 $100

The formula result should be:
10/1/09 $700
10/2/09 $100
10/3/09 $175


I have a problem using multiple formulas at the same time, can't ever get them to work right.

Thanks again for being my continued source of excel genius......I of course take credit here in the office!!!

[sweep]

Hi,

Will SUMIF work for you?

=SUMIF(A2:A8,cell_containing_date,B2:B8)

[NBVC]

Pivot tables might be an option.. this creates a unique list of dates and their respective counts/sums...

http://peltiertech.com/Excel/Pivots/pivotstart.htm

[Georgia Golfer]

OK, I think I figured out how to create a pivot table & it shows the dates & cash in's, & has a field for daily total, but the cell is blank. Also, it does the date by day, but by number, i.e. the 10th -19th is before the 2nd.

[Georgia Golfer]

To add, I have date and cash in as colum labels, but if I add cash in to the sigma value, it only gives me a count of the number of transactions for that day, not the sum of cash ins.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Georgia Golfer]

I've attached a sample, thanks

[NBVC]

The Cash-Ins field does go in the Sigma section.. in the field list, select the Cash-In field that is in the Sigma section, from the menu select Value Field Setting and choose Sum.

You can also right click the field within table and select Value Field Setting.

[NBVC]

Also, if you right click and select Sort.. it should fix the sorting.

[Georgia Golfer]

OK, Got it summing up right, thanks. What about for the dates to get them in proper order? Any advice?

[NBVC]

See my last post...

[Georgia Golfer]

I tried that, no luck. I've tried reformatting the cells to a mm/dd/yyyy format & they don't change....really weird.

[NBVC]

I am not sure why it would not work..

What I did was change the formatting of column A to general.. so you see the date serial numbers,

Then I refreshed the table so that the date column appeared as serial numbers, then I sorted that table, I reset the format in column A to date and refreshed the pivot table again... and voila....