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

Question:

# Question:Q: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?

Andreas

MacBook Air, iOS 11.0.3

Posted on

Question marked as Solved

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

Posted on

Hi Andreas,

Here is one way (perhaps not quite what you want).

This table has a Header Row and a Footer Row.

Add extra columns to the Source table and type the search words in Row 1

Formula in C2 (and Fill Down and Fill Right)

=IFERROR(SEARCH(C\$1,\$A2),"")

SEARCH finds the starting position of a search word (keyword or keywords or Keywords or KEYWORD).

Use the FIND function if upper/lower case is important.

IFERROR inserts "" (NULL) if SEARCH can not find the string.

Formula in Footer Cell C11 (and Fill Right)

=COUNTIF(C,">0")

With Header and Footer Rows, you can add more Body Rows and the formulas will automatically adjust.

Regards,

Ian.

There’s more to the conversation

Hi Andreas,

Here is one way (perhaps not quite what you want).

This table has a Header Row and a Footer Row.

Add extra columns to the Source table and type the search words in Row 1

Formula in C2 (and Fill Down and Fill Right)

=IFERROR(SEARCH(C\$1,\$A2),"")

SEARCH finds the starting position of a search word (keyword or keywords or Keywords or KEYWORD).

Use the FIND function if upper/lower case is important.

IFERROR inserts "" (NULL) if SEARCH can not find the string.

Formula in Footer Cell C11 (and Fill Right)

=COUNTIF(C,">0")

With Header and Footer Rows, you can add more Body Rows and the formulas will automatically adjust.

Regards,

Ian.

Nov 13, 2017 6:21 AM

Hi Ian,

thank you for the reply and this interesting solution 🙂

Still it is not 100% scalable in my case, as I have datasets with a huge amount of information 1000+ rows which makes this approach somewhat unpractical.

Many greetings, Andreas

Nov 13, 2017 6:24 AM

Question marked as Solved

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

Nov 13, 2017 7:02 AM

Wow, yeah!

So simple and clean, super easy!

Thanks so much! 😁

Nov 13, 2017 7:02 AM

User profile for user: a_me

Question: Search for a string in a column and sum values