- Morning all.
I have a google sheet with lots of SUMIF formula across the page in cells, and I getting fed up having to expand the range of these so trying to look at converting to using the QUERY function instead.
I have one sheet with trade data. Columns are
- Trade date/time stamp
- Trade direction
- Security id
the second tab i am trying to convert to a query needs to have the security ID along the top row, and each day down the left side without time stamp. So for this week, even if so only traded on the 18/20th January 2021, there would be a data row for the 19th as well.
then for the data in the table, this is a sum of all the Units bought less sold up to the end of that date.
Any ideas how I do this?
=TRANSPOSE(QUERY(Trades!A:B,”Select C, SUM(D) where B = ‘Buy’ group by A”))
So that formula if I have typed it in right would put all the Security ID’s along the top row, all the trade dates with time stamp down the left, and only fill in the data fields on the days I have a buy. It wouldn’t sum up the buys prior to that day.