# Select a cell that is to the left of a cell when searching for a certain keyword

I am wondering if there is a way to select a cell that is a column two cells away by searching for a keyword.

The scenario is this, I have a budget spreadsheet that I use to budget daily. I want to make a cell do a quick sum calculation for any cell that is detailed as having the keyword “Gasoline” in it.

Ex. My spreadsheet has column A which is the money field (\$ or -\$ depending on if it’s income or expenditure), coulumn B is Date, and Column C is the details of the income/expenditure.

Cost | Date | Details

-\$45.75 | 2020-01-16 | Gasoline fill up at Esso

I want to create a SUM formula that picks up any cell that is in C that has the word Gasoline, and takes it’s corresponding column A cell adding to a running total. So really, the column C (details) is just a way of finding the corresponding column A field.

I’ve been trying to research a way to do this, but really don’t know what keywords I should use to find this way of calculation.

Hopefully the above makes sense.

Thanks!

Hello,

assuming (for the formula provided in this answer)

• Column `A`: Cost
• Column `C`: Details
• Data set in rows `2` through `30`

use the following formula `=SUMIFS(A2:A30,C2:C30,".*Gasoline.*")`

Additional pre-requisite: `Tools -> Options -> LibreOffice Calc -> Calculate -> Option: [x] Enable regular expressions in formulas` is set.

Tested using LibreOffice:

``````Version: 6.4.0.3, Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
``````

Hope that helps.