calculating month end value for stock portfolio from list of transactions

I have a list of trades I have made with the buy/ sell dates and values. This portfolio holds 2 stocks and at month's end it will have 1 or 2 stocks and/ or all Cash. Cash is prices at $1 initial positions are in Cash for $10,000 each.

I now need to calculate on a monthly basis the portfolio value as well as yearly as these trades go back to 2007. I have a vlookup to find the month end stock price*shares:

=VLOOKUP(WORKDAY(DATE(YEAR(B18),MONTH(B18)+1,1),-1),Prices!$A$1:$S$1911,MATCH(A18,Prices!$A$1:$S$1,0),0)*D18

where Prices is a worksheet which has all the prices for all securities in portfolio. Col A is date Col B on are stock prices and Row 1 has the stock symbol. (FYI CASH is not a stock symbol and is always $1.

I can use sumifs to add the buy or sell values, which is incorrect but don't know how to insert the code above into the sumifs. See attached spreadsheet for formula.

monthend calculations.xlsx

Any help is greatly appreciated.
Doug

Not really sure what your expected answers would be, but see if this gets you any closer...
=SUMIFS($I$18:$I$200,$B$18:$B$200,"<="&M3,$F$18:$F$200,">"&M2,$A$18:$A$200,"<>cash")

OK so what would your expected answer look like?

Is that based on the WB you uploaded? Because none of the values you quotes are in that WB

The 4/30/07 prices for RSP was 50.69 and SLY was 65.19 for 5/31/007 the prices for QQQ was 47.41 and SPY was 153.32. The number of shares is calculated correctly based on the sale proceeds on the date of purchase.