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

[NGWNN]

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!

[anon73440385]

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.

If the answer helped to solve your problem, please click the check mark () next to the answer.

[NGWNN]

Amazing! Thank you so much. This did exactly what I was hoping to achieve. I just ran it against my manual check and it’s the exact same answer. This is going to be so useful. Thank you again!