• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to classified the deals with same maturity date together

D

dingtang

New Member
I have a table of data on the maturity date of some trading deals. May I know how can i classified those trading deals with the same maturity date together ? For example:

02 oct 2011 100

25 nov 2011 20

02 oct 2011 400

17 dec 2011 1000

03 jan 2012 500

02 oct 2011 70


Into 02 oct 2011 570

25 nov 2011 20 and etc

Thanks
 
It depends on how the date is field is set up. But for the most part, you can sumif, or sumifs/sumproduct on a unique list of dates.
 
Thank you for your reply. If i use sumif function, i can set the range but how do i set the particular date ? Can u show me how to write the function or not ? Thanks again.
 
It's pretty straight forward:


1. Create a summary worksheet

2. Put a unique list of dates in column A

3. Column B should use formula =sumif(DataSheet!DateColumn,a1,DataSheet!DataColumn)


Come to think of it, you could also probably do this with a pivot table.
 
Hi dan, thanks for your reply. Do u mean i create the data of the maturity date and the amount and save it into another excel file and then open a new excel file and key in the function you had written for me ? You also suggested me to use a pivot table, may I know how do i go about doing it? I am not familiar with pivot table.


Thanks again.
 
ding:


You would create the unique list of dates on another worksheet.


To make a pivot table, just start the wizard, select your data, and click finish. Put the maturity date in the row headers, drop the value into the....well.....value field.
 
I follow your instructions by creating the dates on another worksheet and save it as test.xls. But when i type in the function you had written for me earlier,it gave me an error message stating that MS excel cannot file 'datacolumn' on test.xls.


Can you show me my mistakes please ? Thanks.
 
Replace datacolumn with whatever column your data is in. So.....


If you're date is in column A and your value is in column B, your formula should be:


=sumif(DataSheet!a:a,a1,DataSheet!b:b)


Also, the DataSheet! is referencing whatever sheet your data is on. You may need to rename it or change the name.
 
You must log in or register to reply here.
Back
Top