 # How do I calculate select cells of one column based on the corresponding cell of another column containing specific values?

I have a spreadsheet with keyword search terms in column A, and values of monthly search volume in column B.
How do I SUM all cells in column B, if the corresponding cell in column A contains a specific keyword (ie: “libre”)?

``````-------------------------------------
A            |     B
-------------------------------------
Keywords         | Volume
-------------------------------------
libre office     | 7
libre calc       | 9
office excel     | 5
``````

So in the above example my result would return value 16 (7+9), as both “libre office” (val. 7) and “libre calc” (val. 9) contain the text “libre”.

Three ways…

``````=SUMIF(A1:A3,"=libre.*",B1:B3) (this is probably the solution you are looking for)

=SUMIF(A1:A3,"=libre office",B1:B3)+SUMIF(A1:A3,"=libre calc",B1:B3)

=SUMPRODUCT((A1:A3="libre office")*(B1:B3)+(A1:A3="libre calc")*(B1:B3))
``````

A fourth way should be:

``````=SUMIFS(B1:B3,A1:A3,"=libre office",A1:A3,"=libre calc")
``````

but this one doesn’t work for me. I get zero. Someone else explain SUMSIFS as to why or why not use it?

Could you indent the lines with code with four spaces, so that it is recognised and rendered as code? That should also return the * in the formulas (although adding spaces around asterisks should work too)