# 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