Google Sheets sum of a cell range using values from another tab

0
votes

I am creating a Google Sheet that (among other things) looks at a list of clients, what products they are currently signed up for and calculates the potential value of products we could upsell them to.

I have an example spreadsheet setup here.

The way I want it to work, for each client row, is for the "Sum" cell to add up the values for each Product where the cell contains "Upsell".

The product value to use is then listed on the second tab. I have this as a separate table because values may change in the future or products may be removed or added. And so ideally, I would like to edit the values here as opposed to editing the values within a formula.

However, I am drawing a total blank as to what function I should be using here.

2
google-sheetsgoogle-sheets-formula
We don't have permission to see the document that you sent . Please make it public with a shareable link. if this is not possible then please attach some screenshots . – soMario
You most likely need a sumif together with vlookup or index(match) . Sumif will check for the condition and vlookup or index (match) will get the value of the second tab according to a given condition. – soMario
@MariosKaramanis Apologies, I thought my document was public but it should be now: docs.google.com/spreadsheets/d/… – Megan

2 Answers

0
votes

Approach

The idea is to get the Product name from the LIST table and then get its correspondent price form the VALUE table, when the Cell value is "Upsell".

First I will get the COLUMN index of all the cells whose value is "Upsell".

Then I will use this COLUMN index as a VLOOKUP formula's index to retrieve the Product names form the headers row.

Next, I will use another VLOOKUP to get the correspondent prices.

Finally I will sum the values in the row with a SUM formula.

Formula

=ARRAYFORMULA(
    SUM(
        IFERROR(
            VLOOKUP(
                VLOOKUP(
                    "Product 1",
                    $C$1:$F$1,
                    IF(
                        $C2:$F2="Upsell",
                        COLUMN($C2:$F2),
                        ""
                    )-2,
                    FALSE
                ),
                VALUE!$A$2:$B$5,
                2
             )
         )
     )
)

Just drag the formula down for each costumer and you will get the correct amounts.

0
votes

If I understand you correctly, you want something like this:

  |   A    |     B    |     C    |     D    |    E   |  F  |     G    |    H   |
1 | Client | ProductA | ProductB | ProductC | Upsell |     | Products |  Cost  |
--------------------------------------------------------------------------------
2 | Jordan |  Upsell  |          |          | $12.00 |     | ProductA | $12.00 |
3 | Pippen |          |  Upsell  |          | $23.00 |     | ProductB | $23.00 |
4 | Rodman |  Upsell  |          |  Upsell  | $46.00 |     | ProductC | $34.00 |

This can be done with an array formula in cell E1 that looks like this:

={"Upsell";filter(
  mmult(0+iferror(1*(B:D="Upsell")),
    filter(H:H,(row()<row(H:H))*len(H:H))
  ),(row()<row(A:A))*len(A:A)
)}

Notes: My method automatically fills the entire column with results - no need to copy the formula down. It should be trivial to reference your product cost table from another sheet, but this assumes the products appear in the same order. If it were me, I would enforce that assumption by using the following formula in cell B1:

=transpose(filter(G:G,(row()<row(G:G))*len(G:G)))