2

I'm looking to get the difference (profit) between BUY and SELL for each day (column A). I've managed to get overall difference between BUY and SELL for all days but would like have each day calculated in totals per day in B:9 to B11.

So far I have this formula below but it just calculates total difference on all days between BUY and SELL In Column B.

=SUMIF(C:C,"SELL",H:H)-(SUMIF(C:C,"BUY",H:H))

How can I alter this function to reference the totals only for the specific date in column A?

enter image description here

0

Solution with two functions:

enter image description here

1. Get list of dates

Formula in E2 with QUERY to get all unique dates:

=QUERY(A2:A, "SELECT MAX(A) WHERE A != '' GROUP BY A LABEL MAX(A) ''")

2. Get difference between "SELL" and "BUY"

Formula in F2 with ARRAYFORMULA to get difference between "SELL" and "BUY":

=IF(ISBLANK($E2),"",ARRAYFORMULA(SUM(IF(EQ(A2:A,$E2),IF(B2:B = "SELL", C2:C, MINUS(0, C2:C)),0))))

Drag this formula down to all needed cells in F column.

Note:

format of "Date" column should be set to "Plain text", format of "Order Price" values should be "Number".

  • That's just what I was looking for. Works great. Thanks. – Justin Jan 15 '18 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.