I have the following which works, it’s hard coded to look for the word “dog” but how can I replace “dog” with the value within a cell?
=SUMIFS($E$3:$E$36,$C$3:$C$36,".dog.")
=SUMIFS($E$3:$E$36,$C$3:$C$36,"." & $C$1 & ".")?
thanks, but this didn’t work,
If F2 content is .dog. use =SUMIFS($E$3:$E$36;$C$3:$C$36;F2).
Why the dots before and after dog?
Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.
Edit: Just replaced comma by semicolon in the formula.
Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.
Check the mark (
→ 
) to the left of the answer that solves your question.
thank you,
The semi-colon did not work for me, i have to use comma, is it because I’m using linux version?
=SUMIFS($E$3:$E$36,$C$3:$C$36,B55)
This works so long as the contents of B55 match exactly to the contents in the range, eg.
If range has the text “dog and cat” and B55 has only “dog” it will not work. This is why I had the dots like this…
=SUMIFS($E$3:$E$36,$C$3:$C$36,".dog.")
In B55 use .*dog.*.
You will need to check Enable regular expressions in formulas. To do that choose menu Tools - Options… - LibreOffice Calc - Calculate, and look below Formulas wildcards.
For the comma & semicolon issue, choose menu Tools - Options… - LibreOffice Calc - Formula, below Separators. Semicolon allows better interoperability than comma.
Looks a little funny having .* but it works. Thank you very much.