Sort,Count & Sum data based on unique date

**excelkeechak** · 07-25-2009, 08:58 AM

I had a worksheet which contains date,Item,Issued/purchased,Qty,Amount in the next columns.

i want to have the Count of the Qty & the sum of the Amount for Issued / purchased seperately for change in date every 3 days.

That is,the data should be sorted in ascending order forming the group of three unique dates each(Entries can be any).

**kellyfspringer** · 07-25-2009, 10:23 AM

So, After it is sorted by date,
Column F could have (for QTY):
=IF($A2=$A1,"",SUMIF($A:$A,$A2,D:D))

Column G could have (for Amount):
=IF($A2=$A1,"",SUMIF($A:$A,$A2,E:E))

The way I wrote the formula, you could just copy the first formula and fill it to column G, then down.

Hope this helps!

**stanleydgromjr** · 07-25-2009, 10:42 AM

excelkeechak,

See the attached workbook "Sort Count Sum data based on unique dates - SUMPRODUCT - excelkeechak - SDG09.xls" with a command button "Sort".

Only change the date in cell G2.

Then click on the "Sort" button.

**excelkeechak** · 07-25-2009, 11:06 AM

Thanks a lot for u r replies Mr.Stanley & Kelly.But my requirement is different from the solutions that u had given.

I m refining my requirement. see the attached file for reference. My requirement looks exactly like the attached sheet

Thanks in advance for the replies.

**stanleydgromjr** · 07-26-2009, 12:45 AM

excelkeechak,

Last try!

See the attached workbook "Sort Count Sum data based on 3 dates - insert row for Count Sum - excelkeechak - SDG10.xls" with the new macro "UpdateData".

Run the "UpdateData" macro.

**excelkeechak** · 07-26-2009, 06:57 AM

Thanks a lot mr.stanley for u r code. the code works very fine to me.

How about a little more change in the requirement.if i want the issues on the top then the purchases in the bottom.The rest remains the same,i.e. All the issues should be grouped according to three different (Unique) dates & the count of the items with the amount column summed for the three dates under it while inserting the row under the dates.

I got an idea regarding this problem,can we sort the data based on the ISSUES/PURCHASES column.Autofilter it.Paste it into two groups like ISSUES on the top with the unique dates(3 Unique dates with many entries make a group),PURCHASES at the bottom with the unique dates (3 Unique dates with many entries make a group).

Each group will then be COUNTED & SUMMED based on the three unique dates. i m not sure regarding the code. Could u generate the code based on this logic.