functiongoogle-sheets

How can I get adjacent values from one sheet and sum them up based on the content of another cell in the current sheet?

So what I exactly want to do is to be able to enter the item codes of our products into a cell. Now the cell adjacent to that, will then get the prices of our products which is on another sheet. That sheet also contains the item numbers so I believe I can use that I just don't know how.

Each item has a Retail and Wholesale value. I want to get those values and then sum them up.

Solution

  • In E4 you enter:

    =SUM(
    ARRAYFORMULA(ISNUMBER(FIND(Listings!$A$4:$A$33,C4)) * 
    IF(D4="Retail",
       Listings!$C$4:$C$33,
       Listings!$D$4:$D$33
    )
 )

    It will check if the Item Number is in your list and Return True or False in an array.

    Since it is the same as 1 and 0 we multiply that with either the Retail or Wholesale value.

    Of that we take the sum.