Forum Discussion
Solved
Help with formulas based on criteria
I have a Stock Options workbook with several sheets and I'm looking for a formula that will draw info from some of the sheets into a Summary page. For instance, I have one sheet titled ProfitGrid where I track the gains and / or losses from each stock ticker and each strategy. As an example, one of the sheets is titled Puts and I want to where column A has the stock tickers of each of the positions and column lists the actual profit or loss from each position. Column AK is the Status column which shows whether the position is Open or Closed. So, I created the following formula to be used on the Profit Grid sheet: "=SUMIFS(Puts!AF:AF,Puts!A:A,"=""A4",Puts!AK:AK,"<>""Open")". Column A of the Profit Grid sheet also lists the stock tickers and Column B is the puts column. Cell A4 contains the stock ticker AAL and this formula is in cell B4 in an attempt to show the profit or loss from AAL puts. Thanks in advance.
Paladin1078 Yes, attachments are available not for all users. You may share your file on OneDrive, Google Drive, whatever. Give access to everyone and share the link.
- Here's a much simpler spreadsheet. I simply want Excel to search the A column of the Puts, Calls, etc page for the ticker symbol in the A column of the ProfitGrid page and report the profit or loss from the AF column of the Puts or Calls page IF the AK column for the Puts page contains anything other than "Open" and report that total in cell B4 of the ProfitGrid page. Or, in the example of the calls page if the report the results in cell C4 of the ProfitGrid page. In the example sheet there is just data for a few trades but, in the real sheet there would be hundreds of trades. https://1drv.ms/x/s!AgkZn08NrdddlE7gUkiQvPU2nQve?e=zj1IEv
Okay, thank you.
That could be
=SUMIFS(Puts!$AF:$AF, Puts!$AK:$AK, "<>Open", Puts!$A:$A, $A4 )in B4 and drag it down, similar for Calls. Please check in attached.
- YES! Exactly! Thanks so much.
- @Harun24R
K, here is a copied link: https://1drv.ms/x/s!AgkZn08NrdddlEub9hJ0vmD9iDbR?e=T2zoS7
This workbook is definitely a "work in progress". Primarily, I'm looking for a formula for cell B4 of the ProfitGrid sheet which I can then expand to all the other cells in that sheet which will allow me to track the profitability, (or lack thereof) of each strategy for each stock. In addition, I'm hopeful I can modify the formula to track the market value of current positions on the Summary Sheet in cells Q4 through Q24. Thanks for any and all assistance.,It looks like you are Google Sheets since you use functions like QUERY(). File won't help a lot, opened in Excel it has a quite many errors.
- It was originally a Google sheet that I exported over to Excel. As I stated it's a work in progress as I'm converting everything over to Excel. The LARGE majority of errors is because I deleted much of my personal trading history. But, each of the primary sheets such as Puts, Calls, Leaps, etc should work just fine as they have been converted over to work with Excel and with IBKR's API which is where the option quotes come from. It should at least give anyone who is experienced with Excel a good idea of where I'm trying to get to. The formula is pretty simple, I'm just not experienced enough to know what it is.
- Can you share a sample workbook showing your desired output?
Maybe I don't have enough privleges or something to do that? At any rate, my screen doesn't show that option.
Paladin1078 Yes, attachments are available not for all users. You may share your file on OneDrive, Google Drive, whatever. Give access to everyone and share the link.
Harun24HR
I have no problem doing that if only I can figure out how. Do you mean doing it with photos?
