Finding Date and valuation on 2 condtions each

**rishiambekar** · 04-20-2018, 11:47 AM

Hello Friends,
Further in building a portfolio tracking sheet for tracking mutual fund, I need your help in finding following 2 things automatically, I will explain again that in detail here.

Sheet consist of data entries which are:
1. Every month there will be some money invested and that I named as "SIP".
2. And at some point of time I will withdraw that money which I labelled as "Sell".
Please have a look into the test sheet attached here.

The data here contains column D as sell or SIP/Sell transactions, Column E is amount invested or withdrawn, column F has Net asset value (NAV) and column G contains Units allotted. Now I want to write 2 formulae to auto calculate the Date as well as updated valuation of investment depending on following cases:

A. Date calculation cases:
1. If Column D has one or more "Sell" entries, then the date should be the latest date against the rows which has "Sell" in column D. Here in attached sheet it should be 4/14/2018 (answer).
Or
2. If column D doesn't have "Sell" entry, the formula should give the latest Date entered in table.

B. Now for the Valuation calculation:
1. If Column D has one or more "Sell" entries, then the Valuation should be sum of all entries of column E against "Sell" in column D. Here in attached sheet it should 37535.8+1256.448=38792.748 (answer).
Or
2. If column D doesn't have "Sell" entry, the formula should give the answer = (Sum of Column G entries) * (Latest NAV .i.e last entry in column F).

I hope I am being clear on the question that I want to ask. Let me know if you need any more information on this.

**JohnTopley** · 04-20-2018, 12:11 PM

Try

in I2 (Value)

=IF(COUNTIF($D$4:$D$100,"Sell"),SUMIF($D$4:$D$100,"Sell",$E$4:$E$100),SUM($G$4:$G$100)*LOOKUP(2,1/(F:F<>""),F:F))

in J2 (date)

=IF(COUNTIF($D$4:$D$100,"Sell"),MAX(IF($D$4:$D$100="Sell",$C$4:$C$100)),LOOKUP(9999999,$C$4:$C$100))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**rishiambekar** · 04-20-2018, 10:15 PM

John,
Thank you very much for your prompt reply. I have tested both the formulae and Date formula (in cell J2) is working perfectly fine.

Problem with Valuation formula is that if you change the column D entries from "Sell" to "SIP" (from Cell D35 and D38) then the formula should sum all the entries in column E (as shown in cell E2). I have attached the sheet for you reference.

Appreciate your time and efforts in solving it for me.

**JohnTopley** · 04-21-2018, 02:13 AM

If column D doesn't have "Sell" entry, the formula should give the answer = (Sum of Column G entries) * (Latest NAV .i.e last entry in column F).

Changed your requirement !!!

=IF(COUNTIF($D$4:$D$100,"Sell"),SUMIF($D$4:$D$100,"Sell",$E$4:$E$100),SUM($E$4:$E$100))

**rishiambekar** · 04-21-2018, 09:12 AM

John,

Sorry for inconvenience caused due to changed requirement. I checked, all formulae works as per requirements. Thanks for you prompt and precise replies.

I must say this forum is really of a enthusiast and knowledgeable experts like you. Some other forums They take days and days to answer the question.

Finding Date and valuation on 2 condtions each

LinkBack

Thread Tools

Rate This Thread

Display

Finding Date and valuation on 2 condtions each

Re: Finding Date and valuation on 2 condtions each

Re: Finding Date and valuation on 2 condtions each

Re: Finding Date and valuation on 2 condtions each

Re: Finding Date and valuation on 2 condtions each

Thread Information

Users Browsing this Thread

Similar Threads

If Condtions & Loop

[SOLVED] Request help finding text next to a date, finding the same date in another column...

valuation using active date and quantity

Lookup with two condtions

Average with two condtions

[SOLVED] checking two condtions

[SOLVED] SUM IF 2 CONDTIONS

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions