Ask Your Question

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

asked 2014-01-06 14:39:59 +0100

kaipee gravatar image

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2014-01-06 18:36:26 +0100

Rugslug gravatar image

updated 2014-01-08 01:06:29 +0100

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?

edit flag offensive delete link more


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)

bencomp gravatar imagebencomp ( 2014-01-07 22:57:28 +0100 )edit

Question Tools

1 follower


Asked: 2014-01-06 14:39:59 +0100

Seen: 10,818 times

Last updated: Jan 08 '14