SOLVED

I am trying to search and sum values

Copper Contributor

Hi, 

I am tying to make a search and sum some datebase.

 

for example:

ABCDE
fekaufland5 Namirnice 
wolt6 kaufland 
kaufland4 lidl  
konzum8 konzum 

 

I need to seach for all words in column D in column A and when words are found sum there values and put it in E1. 

I tryed with one word and its working. This is formula I used:  =SUM(IF(ISNUMBER(SEARCH("kaufland";A2:A5));B2:B5;""))

 

But when I try to search for more words its not working.

This is formula I am trying:   =SUM(IF(ISNUMBER(SEARCH(D2:D5;A2:A5));B2:B5;""))

 

Can someone help?

7 Replies
best response confirmed by Carpusina (Copper Contributor)
Solution

@Carpusina The SEARCH part in your second formula returns an array of numbers or #VALUE errors. The IF function gets rid of the errors leaving a number when the words are matched in the same row. In your example only row5 has matching words "konzum" in both column A and D. The array that will be returned has three blanks and the number 8. SUM then returns 8.

 

You probably want to make use of a dynamic array function that will spill the result all at once.

 

Try this one in E2 and see if that does what you want:

 

=SUMIF(A2:A5;"*"&D2:D5&"*";B2:B5)

 

Hi, thank you for response. Your formula search and sum values but put answer in each row of searched word. I would like to put sum of those words that are found in one cell.
For example that I gave, I would like to put it in E1 and that results would need to be 17.
I hope I explained good.

 

I tried to add sum before and worked great. 

Thank you very much.

@Carpusina Then that could be:

=SUM(SUMIF(A2:A5;"*"&D2:D5&"*";B2:B5))

 

 

@Carpusina 

Or, using your original formula:

=SUM(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5))*B2:B5)

or

=SUM(IF(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5)),B2:B5))

If you don't have Office 2021 or Microsoft 365, confirm the formula by pressing Ctrl+Shift+Enter

I have one more question if you maybe know. Can I somehow add condition that wont look at blank cells in column D?

@Carpusina 

=SUM(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5))*(TRANSPOSE(D2:D5)<>"")*B2:B5)

@Carpusina The easiest would be to make sure you don't have blanks in column D. Why would you include blanks in a list to keywords to search for?

 

While writing this, I notice that @Hans Vogelaar came up with a solution that excludes blanks.

1 best response

Accepted Solutions
best response confirmed by Carpusina (Copper Contributor)
Solution

@Carpusina The SEARCH part in your second formula returns an array of numbers or #VALUE errors. The IF function gets rid of the errors leaving a number when the words are matched in the same row. In your example only row5 has matching words "konzum" in both column A and D. The array that will be returned has three blanks and the number 8. SUM then returns 8.

 

You probably want to make use of a dynamic array function that will spill the result all at once.

 

Try this one in E2 and see if that does what you want:

 

=SUMIF(A2:A5;"*"&D2:D5&"*";B2:B5)

 

View solution in original post