Ask Your Question
1

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 +0200

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
1

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

Rugslug gravatar image

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

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

Comments

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 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 10,213 times

Last updated: Jan 08 '14