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

Question:

Question: 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

Reply
Question marked as Solved
Answer:
Answer:

you can try something like this:

User uploaded file


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

Question marked as Helpful

Nov 13, 2017 6:21 AM in response to a_me In response to a_me

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.

User uploaded file

Formula in Footer Cell C11 (and Fill Right)

=COUNTIF(C,">0")

User uploaded file

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

Read all replies

Page content loaded

Question marked as Helpful

Nov 13, 2017 6:21 AM in response to a_me In response to a_me

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.

User uploaded file

Formula in Footer Cell C11 (and Fill Right)

=COUNTIF(C,">0")

User uploaded file

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

Reply Helpful (1)

Nov 13, 2017 6:24 AM in response to Yellowbox In response to Yellowbox

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

Reply Helpful
Question marked as Solved

Nov 13, 2017 7:02 AM in response to a_me In response to a_me

you can try something like this:

User uploaded file


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

Reply Helpful (1)
User profile for user: a_me

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