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.

2 Answers 2


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.


You can use the SUMIF function

Syntax: SUMIF(range, criterion, [sum_range])

=SUMIF(B:B, "joe", A:A)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.