Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 1

7 points

Search for a string in a column and sum values

Hi,

I have googled this but can not really find a workable solution. Please point me to the right direction if this question has been asked already.

I have the following Source table:

Description	Value
This is a cell with content and 1 keyword	10
An another string is here	15
Some more content goes in this	5
Let us repeat another string …	20
And maybe the keyword too	50
Who knows what comes next	10
Maybe new content here	30

And another table with the Results:

Description	Search value	Total
Sum all the cells values with keyword	keyword
Sum all the cell values with string	string
Sum of all the cells with content	content

I would like to search the Descriptions of the Source Table and sum all the cells which contain the Search Value defined in the Results table.

I know how to sum values with the sumif function. I can not find a way, so that the condition of the sumif function searches the content of the description cell and returns TRUE if a cell contains the search value to be summed.

Does that make sense? Is it possible to achieve something like this?

Thank you in advance.

Andreas

MacBook Air, iOS 11.0.3

Posted on Nov 13, 2017 3:14 AM

Best reply

Wayne Contello

Level 7

26,529 points

Posted on Nov 13, 2017 7:02 AM

you can try something like this:

Name the table with the phrases and values "Data", and make the table that summarizes the information "Summary"

Make the first row in both tables a header row

for the table "Summary"

enter the search phrase in column A (as shown).

select cell B2 and type (or copy and paste from here) the formula:

=SUMIF(Data::A, "*"&A2&"*",Data::B )

shorthand for this is:

B2=SUMIF(Data::A, "*"&A2&"*",Data::B )

to fill this formula down, select cell B2, copy

select cells B2 thru the end of column B, paste

In the formula above, the argument:

"*"&A2&"*"

joins the search phrase with a preceding asterisk, and a one following.

... so if the search term is "content" then the argument:

"*"&A2&"*"

will result in

"*content*"

the "*" is a wildcard character and will accept any text before (or after) the search term

View in context

4 replies

There are no replies.

Search for a string in a column and sum values