How to take statistics from a sheet using multiple drop down list to narrow search results

10/18/21
Answered - Pending Review

I don't know if my title made any sense, I really only know the basic jargon of google sheets.

I'm using a sheet to track my day trading. I have data validation drop-down-lists for multiple variables such as: date, win/loss, type of win, type of setup, and many, many other market variables.

When I make a trade, I select the day's date in one column, then the day of the week, then the time of day, whether I won or lost, the type of win(if I won), then manually enter the amount of points gained/lost(not a validated list), and so on (picture a).

I haven't gotten very far trying to automatically create statistics for all of these variables, but at a basic level I want to automatically count number of wins/losses, types of wins, points gained/lost, ect. So far I've figured out the =isoweeknum function to automatically associate a date with the rest of the trades in it's week, and the =month function for monthly statistics.

I have a 2nd sheet (picture b) where I can select a week number from a drop-down-list. This is where I'm stuck. I want it to work so that when I select a week, it counts the total number of trades, and the number of each win/loss variation(total, win, loss, 2:1+'s, runners, washes) for all the trades in that week. Since I don't know the jargon, I'm having a hard time searching for videos/posts for the right functions to use. =countifs is the only one I can think of because, for example, to count the number of losses in week 39 it needs to a) identify every trade that is in week 39, b)identify the number of losses within that group of trades. How can I do this? I think once I have the answer for that, counting the number of a certain variation, within another set of variables, I will be able to extrapolate it out for the more complicated statistics I want to find.

A similar but different question arises for counting points (at least in my head). To count the number of points won/lost for week 39, it would have to identify each trade from week 39, then sum the numbers of points in that range (which I enter manually (picture a) instead of using a drop-down-list).

Thank you for any help, I'm really out of my league here.


Answers

  • Hi @dontstall

    Can I clarify, are you using Google Sheets or Smartsheet? If you're using Google Sheets I would recommend posting in the Google Docs Community under the Sheets category for formula help, here.

    Smartsheet has its own functions and formulas so our solutions may not work in a Google Sheet. If I've misunderstood and you're looking for help translating your current Google Sheets into two Smartsheet sheets, please let us know and we'll be happy to help you here in the Smartsheet Community!

    Cheers,

    Genevieve

Sign In or Register to comment.