Excel problem

[sangramkesharijena]

Hi, Kindly assist me to get the result.


Below are the data of the varioius invoices that has been pending report as shown across below.

Eg--> All those invoice amounts where the invoice date is within 30 days from todays date needs to be summarized and shown under "0-30 Days", Eg--> If present day is 1st August 2011, then invoice dated 2nd July 2011 is within one month, The Amount of this invoice will come under "0-30 days".

For getting this prepare, you can create any formula or feature in excel.


Invoice Date Invoice Amount

20 August 2012 100

10 August 2012 150

15 June 2012 249

20 June 2012 1000

20 May 2012 354

01 May 2012 493


(Expected output) (Have the sum of the amount falling under this ageing bracket) )

0-30 days

30-60 days

60-90 Days

above 90 days

 

[Hui]

Sangramkesharijena


Firstly, Welcome to the Chandoo.org Forums


You can do this with Sumproduct

=SUMPRODUCT(($B$10-$A$2:$A$7>=0)*($B$10-$A$2:$A$7<=30)*$B$2:$B$7)


Or download a sample here:

https://www.dropbox.com/s/zqnebc2v4l0184c/Sangram%201302.xlsx


ps: You may want to consider changing your name on the forums so that it doesn't overlap the posts