• 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 capture the amount against last row of same date

Dear Somendra, Narayan, Debaraj and Friends,

Please find attached herewith an excel sheet containing the date and balance for respective date. Here, I need to capture the balance of last row on a particular day, for example if I want to capture the balance of 05/06/2014 result should be 13,789,685.21 which is last row of the same day in attached sheet. In this same scenario I need to capture the balance of rest of dates in attached sheet. For doing the same I used Pivot table option but it is fetching either Max or Min value. Hence I request you to kindly let me know, is there any formula or alternate for doing the same in single shot rather than doing manually ?

Your support in this regard is highly appreciated.

Regards,
Kumar
 

Attachments

  • R &D .xls
    20 KB · Views: 8
I may not of understood your requirements, but is the upload, what you are after?


.
 

Attachments

  • R &D .xls
    59.5 KB · Views: 6
Kumar@raja

Please Try this formula with Array - Ctrl + Shift + Enter

=SUMPRODUCT(MAX((Amount)*(Date="05-06-2014")))

Amount is column B and Date is Column A

Hope it solve your problem

Thanks
 
Hi Satish,

Thank you it's work out thanks for your idea.

Dear Sgmpatnaik Thanks a lot for your formual.

Hi Bobhc.. Thank you for writing.

Over again Thanks a lot to all..................

Kumar
 
Hi Somendra,

It was nice I need the same result................... Have u typed Dates Manually , I could not find any formula Here............Anyway thanks a lot..............

Kumar
 
Hello Kumar,

You can use SMALL function to extract unique dates.

Say, in D1 & E1 enter, Date & Last Amount

D2, then copy down until you get blank

=IF(MAX(D$1:D1)=MAX(A:A),"",SMALL(A:A,1+COUNTIF(A:A,"<="&D1)))

E2, then double click on the right bottom black dot

=IF(D2="","",LOOKUP(2,1/(A$2:A$70=D2),B$2:B$70))

Adjust the ranges or use dynamic range.
 
  • Like
Reactions: GFC
Back
Top