Google-sheets – How to get the sum of the values of cells adjacent to (left of) a given key

google sheets

For example, given the following table, I'd like to return the sum of all cells directly to the left of all cells that contain the string 'joe' (6.0).

  |    A   |    B    |
1 | 2.0    | joe     |
2 | 3.0    | john    |
3 | 4.0    | joe     |

The closest thing I could find is VLOOKUP, but this only searches for the key in the leftmost column of the range, and it only returns the first instance of a match. Essentially I need a function that will search either the entire range or the rightmost column of the range provided (e.g. A:B) for 'joe', and return the sum of the values in the column to the left of where the matches are found.

Best Answer

The function you need to use is the QUERY 

=QUERY(B21:C23, "select sum(B) where C='joe' label sum(B) ''")

TIP: Why do we use label?

The label clause is used to set the label for one or more columns.

Related Topic