CODEWITHSUNDEEP
if-statementgoogle-sheetsgs-vlookup
shorif2000
shorif2000

Reputation: 2654

Reference a cell using IF inside VLOOKUP on Google Sheets

I want to perform a vlookup with an if statement inside. I currently have

=LOOKUP("Metallic Gold Flash Tattoo Arabic",A:A,IF(INDIRECT(ADDRESS(ROW(), E)))<0,INDIRECT(ADDRESS(ROW(), C)),0)

I want to sum up all the units of that product type in column A, however I only want to sum up the ones that have been sold and not bought. Column C is units and E is price. Sold are positive and negative are bought.

I am using Google Sheets.

Upvotes: 1

Views: 356

Answers (2)

Barry O&#39;Kane
Barry O&#39;Kane

Reputation: 1187

You could try a query:

=QUERY(A:E, "SELECT A, SUM(E) WHERE A = 'Metallic Gold Flash Tattoo Arabic' AND C > 0", 1)

I'm assuming that column C contains the positive and negative values indicating sold and bought.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46371

I'm assuming you want to sum column C (quantity of units) when the description matches in column A and col E is > 0

Try using SUMIFS - that should work in both excel and google sheets, i.e.

=SUMIFS(C:C,A:A,"Metallic Gold Flash Tattoo Arabic",E:E,">0")

....or do you want to get the total cost price of those units (Col C multiplied by E for the relevant rows)?

For that you can use this formula in google sheets

=sum(filter(C:C*E:E,E:E>0,A:A="Metallic Gold Flash Tattoo Arabic"))

Upvotes: 2

Related Questions