2

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.

0

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.

| improve this answer | |
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Feb 28 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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