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.