• 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 a range of date and add the total amt

D

dingtang

New Member
I have a report on time deposits where I need to classified according to number of days. For example for 1 to 7 days, 7 days to 1 month, 1 mth to 3 mth etc. Now i am using Data-filter to arrange the maturity date and then i manually classified according to the different category and then add the total fixed deposits amount. May I know is there any shorter way of doing my report ? Thanks in advance
 
Dingtang


Firstly, Welcome to the Chandoo.org Forums


You could use either a Pivot table which has grouping facilities to report between some date ranges

or

write manual formula using Sumproduct

a formula will be like

=Sumproduct((dateRange>date 1)*(dateRange <date 2), Data Range)
 
Thank you Hui for your reply. I am sorry i still dont really understand how to use privot table or sumproduct to do my report. For example, I have a range of data that look like this : I need to classified the total amount according to the number of days, from 1 to 31 days, 32 to 91 days, 92 to 181 days and etc. I manually classified will be from 1 to 31 days, the total amt will be 85 + 694 which is 779.

May I know is that other methods which can help me to do this report more efficiently ?


Thanks .


Day Amt

3 (85.00)

5 -

5 (694.44)

13 -

60 (333.55)

75 (416.03)

88 -

92 -

95 -
 
Use this pattern of formula:

=SUMIF(A:A,">="&LowerBoundary,B:B)-SUMIF(A:A,">"&UpperBoundary)


So, for 1 to 31, would be:

=SUMIF(A:A">="&1,B:B)-SUMIF(A:A,">"&31,B:B)


Note that you could easily use cell references instead of hard coding everything, if you want.
 
Hi Luke, thank you very much for your guidance, I finally able to do the table. May I ask you for another question ? This time the number of days is changed to Date (ddmmyy), may I know is it possible for me to use the above formula you taught me ?


Thanks again.
 
Dingtang

Are the Dates, Dates or Text?

If there text do a Text to Columns first and convert them to dates

The above formulas should then be ok to use.
 
Hi Hui, thank you for your reply. Do u mean I need to convert the date into days first ? For example: Name Mat date Amt

Mary 02 Sep 2011 100

John 15 Oct 2012 3000

May 23 Dec 2011 200

Hong 15 Jan 2012 500

Yen 28 Sep 2011 400


I need to classified the above data into Within 1 to 7 days, 7days to 1 mth, 1 to 3 mth, 3 to 6 mth, 6 to 1 year etc. Can the formula recommended by Luke be used in this case ? Thanks.
 
If the actual value in the "date" columns are dates (ie, you can use the date format, cell evaluates to a number, etc), then the formula I gave can work. If it's simply the text "Sep 09", then you will need to change it into a proper date (ie number), format with custom format of "mmm yy" and then you can use the formula.


If you're always looking forward, I'd setup some cells to act as the boundary markers, like:

=TODAY()+1

=TODAY()+7

=TODAY()+30

etc.


Does that help?
 
Hi Luke, is the formula written as =sumif(A:A,">="&01-Sep-11,B:B)-sumif(A:A,">"07-Sep-11,B:B) ?-(For period within 7 days of the month)


But the answer generated out is 00-Jan-00, may I know what is reason ?


And may I know what do you mean by boundary markers and how can I make use of it ?


Thanks very much for your help again.
 
No. It would work much easier if you input the date into a cell, and then use a cell reference in the formula. The way you have it currently, XL is literally taking 1 minus "Sep" (which evaluates to 0) minus 11 and getting the unwanted value of -10. Either use a cell reference, or use DATEVALUE function:

=sumif(A:A,">="&DATEVALUE("01-Sep-11"),B:B)-sumif(A:A,">"&DATEVALUE("07-Sep-11"),B:B)
 
Thanks Luke again. But when I try using the command you written for me(using the Datevalue function), it generate as 9-Apr-00, and not the value 100.


Sorry for my ignorance, do you mind pointing out my mistake again ? Thanks again for giving advices to me.
 
XL just has the cell formatting switched to date. Format the cell to number, no decimals to display 100.
 
Hi Luke, thank you very much for your patient for guiding me how to solve my problem. I finally managed to generate the correct figures. I hope to learn from you in the future. Nice to know you too.
 
You must log in or register to reply here.
Back
Top