Google Sheets - Using an IF statement in a Query

user_15437183 0
Viewed 120

I am making a spreadsheet for taxes on my crypto purchases and sales. I have the QUERY function to pull the data from my main sheet to my TAX sheet but I need my amount of coins to show up negative on a sale. I want to make a number negative if "sell" is present or keep it positive if "buy". I am trying to use an IF statement inside a QUERY.

=QUERY(Trading!$A$3:H1001, "select A,B,C,D,E,G,H where B contains 'Crypto.com'" & if(Trading!F:F = "SELL", Trading!D:D-1, Trading!D:D1))

Link to Sample sheet

The locked pages are editable but are just used to gather or receive data from the Trading page.

google-sheets-formula google-sheets if-statement
1 Answers

We can only basically guess without access to see your actual sheets, data and layout (screenshots don't help much). That said, conceptually, this should work:

=ArrayFormula(IFERROR(FILTER({Trading!A3:C,UPPER(Trading!D3:D)*-1,Trading!E3:E,Trading!G3:H}, UPPER(Trading!F3:F)="SELL", ISNUMBER(SEARCH("Crypto",Trading!B3:B)))))

If that does not work, I recommend sharing a link to a sample document, being sure to set the link's Share permissions (when you create the link) to "Anyone with the link..." and "Editor."

user_139032830
Related