Complex SUMPRODUCT with Date Conditions

[XcelRookie]

I've been unsuccessful in creating a formula that will deliver the calculated results that I've described below. Hoping someone can help.
I've tried using SUMIF and SUMPRODUCT - maybe there's another function better suited to the task? I'm trying to get a calculation, for each day, for the combined total of realized and unrealized profits for a complete portfolio of numerous stocks. I'm using Excel 2016. To view the sample worksheet, please see the file in the attached URL. What I'm trying to do is to get a calculation, for each day, for the combined total of realized and unrealized profits for a complete portfolio of numerous stocks. I'm using Excel 2016. Here's my attempt to articulate all the formula arguments:

STEP 1 - CALC THE UNREALIZED PROFITS
1. IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS<exit date="" or="" exit="" is="" blank,="" then="" calc="" the="" unrealized="" profit="" as="" of="" query="" by="" taking="" entry="" price="" and="" subtracting="" it="" from="" mtm="" (mark="" to="" market)="" multiply="" that="" sum="" #="" shares.="" return="" an="" error="" if="" required="" blank.
2. IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.

STEP 2 - CALC THE REALIZED PROFITS
1. IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS =>EXIT DATE, THEN PICKUP THE CORRESPONDING VALUES IN THE REALIZED PROFIT COLUMN AND SUM THOSE. (IN THIS CASE, NO NEED TO RETURN AN ERROR IF MTM PRICE IS BLANK WHICH IT MAY OR MAY NOT BE).2. AGAIN, IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.

STEP 3 - SUM THE RESULTS OF THE CALCS FOR UNREALIZED AND REALIZED PROFITS INTO A SINGLE OUTPUT VALUE

Clarification Notes:
1. Note that I've added the Result Calculation Columns K & L just for illustration purposes, all those calcs are intended to take place in the cells above the Query Dates.2. Values associated with Entry Dates that are > than the Query Date are never to be considered in these calculation.
3. Realized profit included above is driven off of fields that weren't included in this sample (so don't try to make sense of them).
*Sometimes (like in cell J12) there will be values in the MTM Price column even if the trade has been exited - I assume that those values will not cause any problems for the above calcs .

HOPE THIS MAKES SENSE - ANY HELP OR SUGGESTIONS GREATLY APPRECIATED!</exit>

 

[XcelRookie]

I've discovered that the link that I've created for this file through a Dropbox URL is not accessible. Will re-post with a capture of worksheet from Excel Jeanie.

 

[XcelRookie]

SUMPRODUCT to Calculate Multiple Products and Sums with Date Conditions

I've been unsuccessful in creating a formula that will deliver the calculated results that I've described below. Hoping someone can help.
I've tried using SUMIF and SUMPRODUCT - maybe there's another function better suited to the task? I'm trying to get a calculation, for each day, for the combined total of realized and unrealized profits for a complete portfolio of numerous stocks. I'm using Excel 2016. Here's a screenshot of my worksheet along with my attempt to articulate all the formula arguments:

A

B

C

D

E

F

G

H

I

J

K

L

M

Query Dates

Stock Name

Entry Date

Entry Price

Exit Date

# of Shares

ABC Co.

Ma & Pa Co.

XYZ Co.

Stardust Co.

New Co.

Startup Co.

Carparts Co.

Food Co.

*

Mining Co.

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: center"] $495.00 [/TD]
[TD="align: center"] $505.00 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]RESULT OF QUERY[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]RESULT OF QUERY[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: center"]Realized[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]25-Aug-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]30-Aug-17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]25-Aug-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]30-Aug-17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: center"]Profit[/TD]

[TD="align: center"]MTM PRICE[/TD]
[TD="align: center"]MTM PRICE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]9-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"] $ 11.00[/TD]
[TD="align: right"] $ 10.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 100.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 25.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]10-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]8/17/2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 10.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 10.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]12-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"] $ 10.50[/TD]
[TD="align: right"] $ 11.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 50.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 100.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="align: right"]13-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"] $ 10.25[/TD]
[TD="align: right"] $ 11.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 25.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 125.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="align: right"]14-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]8/25/2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]135[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 135.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 135.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="align: right"]15-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]8/26/2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"] $ 12.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 225.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 60.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="align: right"]16-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]$ 9.00[/TD]
[TD="align: right"] $ 10.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ (100.00)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 50.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="align: right"]17-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]8/28/2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"] $ 10.50[/TD]
[TD="align: right"] $ 10.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 50.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 100.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="align: right"]30-Aug-17[/TD]
[TD="align: right"]$10.00[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"] $ 9.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ (100.00)[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 495.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: right"] $ 505.00 [/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

STEP 1 - CALC THE UNREALIZED PROFITS
1.IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS<exit date="" or="" exit="" is="" blank,="" then="" calc="" the="" unrealized="" profit="" as="" of="" query="" by="" taking="" entry="" price="" and="" subtracting="" it="" from="" mtm="" (mark="" to="" market)="" multiply="" that="" sum="" #="" shares.="" return="" an="" error="" if="" blank.
< EXIT DATE OR EXIT DATE IS BLANK, THEN CALC THE UNREALIZED PROFIT AS OF THE QUERY DATE BY TAKING THE ENTRY PRICE AND SUBTRACTING IT FROM THE MTM (MARK TO MARKET) PRICE AND THEN MULTIPLY THAT SUM BY THE # OF SHARES. RETURN AN ERROR IF THE MTM PRICE IS BLANK.
2.IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.
PERFORM THIS OPERATION FOR EACH ROW IN EACH COLUMN AND SUM ALL THE RESULTS

STEP 2 - CALC THE REALIZED PROFITS
1. IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS =>EXIT DATE, THEN PICKUP THE CORRESPONDING VALUES IN THE REALIZED PROFIT COLUMN AND SUM THOSE. (IN THIS CASE, NO NEED TO RETURN AN ERROR IF MTM PRICE IS BLANK WHICH IT MAY OR MAY NOT BE).
2. AGAIN, IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.
PERFORM THIS OPERATION FOR EACH ROW IN EACH COLUMN AND SUM ALL THE RESULTS

STEP 3 - SUM THE RESULTS OF THE CALCS FOR UNREALIZED AND REALIZED PROFITS (STEPS 1 & 2 ABOVE) INTO A SINGLE OUTPUT VALUE

Clarification Notes:
1. Query dates are highlighted in yellow.
2. Note that I've added the Result Calculation Columns L & M just for illustration purposes, all those calcs are intended to take place in the cells above the Query Dates.
3. Values associated with Entry Dates that are > than the Query Date are never to be considered in these calculation.
4. Realized profit included above is driven off of fields that weren't included in this sample (so don't try to make sense of them).
*Sometimes (like in cell J12) there will be values in the MTM Price column even if the trade has been exited - I assume that those values will not cause any problems for the above calcs .

Hope this makes sense - any help or suggestions greatly appreciated!</exit>