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

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".

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-18 13:39:05.073529

Sort by » oldest newest most voted 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?

more